Microsoft Power Query For Excel



Welcome to the first Power BI feature summary of the Spring! In this update we have an exciting new preview for the Power Automate visual! Also, the ongoing previews (Small Multiples and DirectQuery for Power BI datasets and Azure Analysis Services) are getting some new features. Next to that, we are presenting huge improvements to the shapes in Power BI Desktop and we are introducing a new. Microsoft Power Query provides a powerful data import experience that encompasses many features. Power Query works with Analysis Services, Excel, and Power BI workbooks. A core capability of Power Query is to filter and combine, that is, to mash-up data from one or more of a. Power Query is available as an add-in to download and install for Excel 2010 and 2013 and will appear as a new tab in the ribbon labelled Power Query. In 2016 it was renamed to Get & Transform and appears in the Data tab without the need to install any add-in. Power Query Books Gil Raviv Gil Raviv, Collect, Combine and Transform Data Using Power Query in Excel and Power BI (Pearson Education, 2019).

The Best Microsoft Power BI courses for beginners to learn Microsoft Power BI in 2021.

Data visualization permits you to address data carefully and graphically, to pass on the consequences of data investigation. Microsoft Power BI is a set-up of business examination apparatuses that help you acquire bits of knowledge from your data. The suite unites natural tasks from notable items—including Excel, Access, Power Query, and Power Pivot—to assist you with getting move the fundamentals and on to complete investigation.

Power BI is rapidly turning into the world's most impressive self-administration business insight stage, and a significant apparatus for data experts and novices the same. With Power BI you can interface with many data sources, assemble complex social models utilizing straightforward and instinctive devices, and configure dazzling, intuitive dashboards without any preparation - just free of charge.

You need to break down data from single or different sources? You need to make your individual datasets dependent on these sources and change your outcomes into delightful and simple-to-make visualizations? You additionally need to impart your outcomes to partners or team up on your tasks? At long last, you need to have the option to get to your data from different gadgets?

At that point, the Power BI instruments are the apparatuses to decide for you!

Disclosure: Coursesity is supported by the learner's community. We may earn an affiliate commission when you make a purchase via links on Coursesity.

Top Microsoft Power BI Courses List

1. Microsoft Power BI with Desktop Training Course

Master Power BI Desktop & learn advanced Power BI analysis & data visualization w/ a top Microsoft Power BI instructor.

Course rating: 4.6 out of 5.0 ( 34,173 Ratings total)

Duration: 10 h 30 m

Certificate: Certificate on completion

In this course, you will learn how to:

  • build professional-quality business intelligence reports from the ground up.
  • blend and transform raw data into beautiful interactive dashboards.
  • design and implement the same B.I. tools used by professional analysts and data scientists.
  • showcase your skills with two full-scale course projects (with step-by-step solutions).
  • understand the business intelligence workflow from end-to-end.

The course includes:

  • Connect & Transform the Raw Data
  • Intro to the Power BI Query Editor
  • Types of Power BI Data Connectors
  • Basic Table Transformations
  • Text, Number & Date Tools
  • Index & Conditional Columns
  • Grouping & Aggregating Data
  • Pivoting & Unpivoting
  • Modifying, Merging & Appending Queries
  • Connecting to Folders
  • Defining Hierarchies & Categories
  • Query Editing & Power BI Best Practices
  • Build a Relational Data Model
  • Intro to Database Normalization
  • Data ('Fact') Tables vs. Lookup ('Dimension') Tables
  • Creating Power BI Table Relationships
  • 'Star' vs. 'Snowflake' Schemas
  • Active vs. Inactive Relationships
  • Relationship Cardinality
  • Connecting Multiple Data Tables
  • Filtering & Cross-Filtering
  • Hiding Fields from the Power BI Report View
  • Data Modeling & Power BI best Practices
  • Add Calculated Fields with DAX
  • Intro to Data Analysis Expressions (DAX)
  • Calculated Columns vs. Measures
  • Row Context vs. Filter Context in Power BI
  • DAX Syntax & Operators
  • Common Power BI Functions
  • Basic Date & Time Formulas
  • Logical & Conditional Statements
  • Text, Math & Stats Functions
  • Joining Data with RELATED
  • CALCULATE, ALL & FILTER Functions
  • DAX Iterators (SUMX, AVERAGEX)
  • Time Intelligence Formulas
  • DAX & Power BI Best Practices
  • Design Interactive Power BI Reports
  • Intro to the Power BI Report View
  • Adding Basic Charts to Power BI Reports
  • Formatting & Filtering Options
  • Matrix Visuals
  • Slicers & Timelines
  • Cards & KPIs
  • Power BI Map Visuals (Basic, Fill, ArcGIS)
  • Treemaps, Lines, Areas & Gauges
  • Editing Report interactions
  • Adding Drillthrough Filters
  • Linking to Report Bookmarks
  • Using 'What-If' Parameters
  • Managing & Viewing Roles
  • PREVIEW: Publishing to Power BI Service
  • Power BI Data Viz Best Practices

In this course, you will be playing the role of Lead Business Intelligence Analyst for Adventure Works Cycles, a global manufacturing company. Your aim is to design and deliver a professional-quality, end-to-end business intelligence solution, armed only with Power BI and a handful of raw CSV files.

The course will guide you through the ins and outs of Power BI Desktop, sharing crystal clear explanations and helpful pro tips each step of the way. You will follow a steady, systematic progression through the Power BI workflow.

By the end of the Adventure Works project, not only will you have developed an entire business intelligence tool from the ground up using Power BI, but you will have gained the knowledge and confidence to apply these same concepts to your own Power BI projects.

You can take Microsoft Power BI with Desktop Training Course Certificate Course on Udemy.

2. Advanced Microsoft Power BI

Learn advanced uses of Microsoft Power BI. Learn how to perform calculations, leverage the DAX language, make advanced queries, and improve querying by using the M language.

Course rating: 135,834 total enrollments

Duration: 3 h 14 m

Certificate: Certificate on completion

In this course you will learn about:

  • Microsoft Power BI, a suite of business analytics tools that help you gain insights from your data.
  • what suite brings together familiar operations from well-known products—including Excel, Access, Power Query, and Power Pivot—to help you quickly get move the basics and on to comprehensive analysis.
  • how to work with calculations, leverage the DAX language, create advanced queries, and use the M language to improve the querying process.
  • how to import data from various sources, compose advanced formulas, manage the fields and columns in tables, use X-factor functions, use conditional statements, filter results, design charts, and more.

The course includes:

  • How the Power BI Platform Works
  • Working with Data
  • Query Editor
  • DAX Language
  • Introduction to DAX Formulas
  • Advanced DAX Calculations
  • Graphing and Dashboard Design

This course guides you through the advanced capabilities of the service. She shows how to work with calculations, leverage the DAX language, create advanced queries, and use the M language to improve the querying process.

It also demonstrates how to import data from various sources, compose advanced formulas, manage the fields and columns in tables, use X-factor functions, use conditional statements, filter results, design charts, and more

You can take Advanced Microsoft Power BI Certificate Course on LinkedIn.

3. Microsoft Power BI - A Complete Introduction [2021 EDITION]

Learn how to use Microsoft's Power BI Tools, including Power BI Desktop, Power BI Pro (Service), and PowerBI Developer.

Course rating: 4.6 out of 5.0 ( 45,238 Ratings total)

Duration: 23 h 30 m

Certificate: Certificate on completion

In this course, you will learn how to:

  • analyze data from different data sources and create their own datasets.
  • comfortably work with the different tools of the Power BI universe and know how the different tools work together.
  • have the required knowledge to dive deeper into Power BI and find out more about its advanced features.
  • know the different tools of the Power BI universe and learn how to use them.
  • understand Power BI Desktop and its components.
  • use the Query Editor to connect Power BI to various source types.
  • work on the Data Model and understand the difference between those two steps.
  • work in the different views of the Data Model.
  • create calculated columns and measures.
  • build relationships between different tables.
  • create a report with different interactive visualization types.
  • use Power BI Service (= Power BI Pro) to create dashboards and to share and publish your results.
  • access your results from multiple devices using Power BI Mobile.
  • create custom visuals using typescript and the Power BI Developer Tools.

In this course, you will learn why Power BI offers you a comprehensive set of Business Intelligence tools for your data analysis goals and how to use these tools to fulfill all of the above tasks - and more. Imagine quickly structure your data, easily add calculations to it, and creating and publish nice-looking charts in just a few minutes.

You can take Microsoft Power BI - A Complete Introduction [2021 EDITION] Certificate Course on Udemy.

4. Power BI A-Z: Hands-On Power BI Training For Data Science!

Learn Microsoft Power BI for Data Science and Data Analytics. Build visualizations and BI reports with Power BI Desktop.

Course rating: 4.5 out of 5.0 ( 12,193 Ratings total)Duration: 4 h 30 mCertificate: Certificate on completion

In this course, you will learn how to:

  • connect Microsoft Power BI to data sources.
  • create Barcharts.
  • create Treemaps.
  • create Donut Charts.
  • create Waterfall Diagrams.
  • create Piecharts.
  • connect Power BI to a variety of datasets.
  • drill down and up in your visualization and calculate data.
  • visualize data in the form of various charts, plots, and maps.
  • convert raw data into compelling data visualizations

Learn data visualization through Microsoft Power BI and create opportunities for you or key decision-makers to discover data patterns such as customer purchase behavior, sales trends, or production bottlenecks.

You'll learn all of the features in Power BI that allow you to explore, experiment with, fix, prepare, and present data easily, quickly, and beautifully. This course begins with Power BI basics. You will navigate the software, connect it to a data file, and export a worksheet, so even beginners will feel completely at ease.

To be able to find trends in your data and make accurate forecasts, you'll learn how to work with hierarchies and time series. Also, to make data easier to digest, you'll tackle how to use aggregations to summarize information. You will also use granularity to ensure accurate calculations.

In order to begin visualizing data, you'll cover how to create various charts, maps, scatterplots, and interactive dashboards for each of your projects. You'll even learn how to join multiple data sources into one in order to combine diverse sources of information in one analytical solution.

Finally, you'll cover some of the latest and most advanced custom visualizations in Microsoft Power BI, where you will create histograms, brick charts, and more.

By the time you complete this course, you'll be a highly proficient Power BI user. You will be using your skills as a data scientist to extract knowledge from data so you can analyze and visualize complex questions with ease.

You can take Power BI A-Z: Hands-On Power BI Training For Data Science! Certificate Course on Udemy. Microsoft office for mac активация.

5. Make Awesome Eye-catching Dashboards from your data using Power BI!

[Power BI] Master Microsoft Power BI in 90 minutes!

Course rating: 4.1 out of 5.0 ( 89 Ratings total)

Duration: 1 h 30 m

Certificate: Certificate on completion

In this course, you will learn how to:

  • get valuable data insights & answers anytime you want from your raw data.
Query

The course includes:

  • Power BI Introduction
  • Power BI High-Level Flow
  • Installing and Signing Up for Power BI
  • Power Query Introduction
  • Power Pivot Introduction
  • Power View Introduction
  • The Big Picture
  • Get Data
  • How to Get More Data
  • Modeling Data
  • Adding Relationships Manually
  • Visualization
  • Visualization Customization
  • More Visualizations
  • Visualization Format and Analytics

You can take Make Awesome Eye-catching Dashboards from your data using Power BI! Certificate Course on Eduonix.

6. Learning Power BI Desktop Online Class

Learn how to connect and transform your data with Power BI Desktop, the powerful data analysis, and visualization software from Microsoft.

Course rating: 135,834 total enrollments

Certificate: Certificate on completion

The course includes:

  • Power BI Desktop and other Power BI tools
  • Install Power BI Desktop
  • Launch Power BI Desktop
  • Get Data
  • Model Data: Shaping, Combining, and Merging Data
  • Create Visualizations and Reports
  • Share Your Work

In this course, discover how to leverage this easy-to-use tool to more efficiently model and visualize data. Learn how to connect various data sources, including Excel, databases, and web data sources like Wikipedia.

Explore how to search and transform your data using the built-in Query Editor. Plus, it will show you how to build and arrange visualizations, create interactive reports, share your work, manage your published files in the Power BI service, and more.

You can take Learning Power BI Desktop Online Class Certificate Course on LinkedIn.

7. Powerful Reports and Dashboards with Microsoft Power BI Course

A comprehensive guide on how to import, transform & visualize data with Power BI, with practical exercises & case study.

Course rating: 4.6 out of 5.0 ( 9,651 Ratings total)

Duration: 6 h 30 m

Certificate: Certificate on completion

In this course, you will learn how to:

  • create powerful reports and dashboards with Microsoft Power BI with a few clicks of the mouse.
  • gain valuable insight and intelligence from your business and marketing data in a fraction of the time of traditional spreadsheets.
  • publish reports and dashboards on the Internet and view using laptops, tablets, or smartphones in minutes.
  • create data models and use the DAX formula language to develop powerful calculations.
  • easily create a wide range of graphs and visualizations using drag and drop technologies.
  • use the Power BI Q & A feature to easily ask questions of your data and provide new insight.
  • use Power BI Desktop, Power BI Query Editor & the Power BI Service.
  • create relationships between related data sources.
  • build reports with various types of aggregations and filters.
  • understand the various types of visualizations possible in Power BI and how to use them.
  • create interactive dashboards with drill throughs and relative date filters.
  • publish dashboards to the web and mobile app.
  • use themes and custom visualizations.
  • about DAX and how to use it to build measures.
  • work with Excel & Power BI to create a dynamic and effective business solution.
  • put this all into practice with a case study about the Corona Virus.

In this course, you will learn the fundamentals needed to work with Power BI effectively. Understand the flow of using Power BI, from connecting to various data sources, importing these into Power BI, transforming the data, and then presenting it effectively.

You can take Powerful Reports and Dashboards with Microsoft Power BI Course Certificate Course on Udemy.

8. Microsoft Power BI, Microsoft Power BI DAX, DAX function

Learn Microsoft Power BI DAX From Beginner to Advanced

Course rating: 4.0 out of 5.0 ( 1 Rating total)

Duration: 3 h

Certificate: Certificate on completion

In this course, you will :

  • understand the concept of DAX and how to create calculated columns, measures, tables, and quick measures.

From the exhaustive list of DAX functions, you will be looking into the most frequently used functions from the family of Date and Time function.

This course is designed in a Problem Statement: Solution format where we start with the problem to which a solution is sought with the help of DAX functions where each component of the expression is clearly explained in easy to understand terms,

You can take Microsoft Power BI, Microsoft Power BI DAX, DAX function Certificate Course on Eduonix.

Hey! If you have made it this far then certainly you are willing to learn more and here at Coursesity, it is our duty to enlighten people with knowledge on topics they are willing to learn. Here are some more topics that we think will be interesting for you!

-->

Summary

ItemDescription
Release StateGeneral Availability
ProductsPower BI (Datasets)
Power BI (Dataflows)
Power Apps (Dataflows)
Excel
Dynamics 365 Customer Insights
Analysis Services
Authentication Types SupportedAnonymous (online)
Basic (online)
Organizational account (online)
Function Reference DocumentationExcel.Workbook
Excel.CurrentWorkbook

Note

Some capabilities may be present in one product but not others due to deployment schedules and host-specific capabilities.

Prerequisites

To connect to a legacy workbook (such as .xls or .xlsb), the Access Database Engine OLEDB (or ACE) provider is required. To install this provider, go to the download page and install the relevant (32 bit or 64 bit) version. If you don't have it installed, you'll see the following error when connecting to legacy workbooks:

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. The 32-bit (or 64-bit) version of the Access Database Engine OLEDB provider may be required to read this type of file. To download the client software, visit the following site: https://go.microsoft.com/fwlink/?LinkID=285987.

ACE can't be installed in cloud service environments. So if you're seeing this error in a cloud host (such as Power Query Online), you'll need to use a gateway that has ACE installed to connect to the legacy Excel files.

Capabilities Supported

  • Import

Connect to an Excel workbook from Power Query Desktop

To make the connection from Power Query Desktop:

  1. Select the Excel option in the connector selection.

  2. Browse for and select the Excel workbook you want to load. Then select Open.

    If the Excel workbook is online, use the Web connector to connect to the workbook.

  3. In Navigator, select the workbook information you want, then either select Load to load the data or Transform Data to continue transforming the data in Power Query Editor.

Connect to an Excel workbook from Power Query Online

To make the connection from Power Query Online:

  1. Select the Excel option in the connector selection.

  2. In the Excel dialog box that appears, provide the path to the Excel workbook.

  3. If necessary, select an on-premises data gateway to access the Excel workbook.

  4. If this is the first time you've accessed this Excel workbook, select the authentication kind and sign in to your account (if needed).

  5. In Navigator, select the workbook information you want, and then Transform Data to continue transforming the data in Power Query Editor.

Troubleshooting

Connecting to an online Excel workbook

If you want to connect to an Excel document hosted in Sharepoint, you can do so via the Web connector in Power BI Desktop, Excel, and Dataflows, and also with the Excel connector in Dataflows. To get the link to the file:

  1. Open the document in Excel Desktop.
  2. Open the File menu, select the Info tab, and then select Copy Path.
  3. Copy the address into the File Path or URL field, and remove the ?web=1 from the end of the address.

Legacy ACE connector

Microsoft Power Query For Excel

Power Query reads legacy workbooks (such as .xls or .xlsb) use the Access Database Engine (or ACE) OLEDB provider. Because of this, you may come across unexpected behaviors when importing legacy workbooks that don't occur when importing OpenXML workbooks (such as .xlsx). Here are some common examples.

Microsoft Power Query For Excel 2007

Unexpected value formatting

Because of ACE, values from a legacy Excel workbook might be imported with less precision or fidelity than you expect. For example, imagine your Excel file contains the number 1024.231, which you've formatted for display as '1,024.23'. When imported into Power Query, this value is represented as the text value '1,024.23' instead of as the underlying full-fidelity number (1024.231). This is because, in this case, ACE doesn't surface the underlying value to Power Query, but only the value as it's displayed in Excel.

Unexpected null values

When ACE loads a sheet, it looks at the first eight rows to determine the data types of the columns. If the first eight rows aren't representative of the later rows, ACE may apply an incorrect type to that column and return nulls for any value that doesn't match the type. For example, if a column contains numbers in the first eight rows (such as 1000, 1001, and so on) but has non-numerical data in later rows (such as '100Y' and '100Z'), ACE concludes that the column contains numbers, and any non-numeric values are returned as null.

Inconsistent value formatting

In some cases, ACE returns completely different results across refreshes. Using the example described in the formatting section, you might suddenly see the value 1024.231 instead of '1,024.23'. This difference can be caused by having the legacy workbook open in Excel while importing it into Power Query. To resolve this problem, close the workbook.

Missing or incomplete Excel data

Sometimes Power Query fails to extract all the data from an Excel Worksheet. This failure is often caused by the Worksheet having incorrect dimensions (for example, having dimensions of A1:C200 when the actual data occupies more than three columns or 200 rows).

How to diagnose incorrect dimensions

To view the dimensions of a Worksheet:

  1. Rename the xlsx file with a .zip extension.
  2. Open the file in File Explorer.
  3. Navigate into xlworksheets.
  4. Copy the xml file for the problematic sheet (for example, Sheet1.xml) out of the zip file to another location.
  5. Inspect the first few lines of the file. If the file is small enough, open it in a text editor. If the file is too large to be opened in a text editor, run the following command from a Command Prompt: more Sheet1.xml.
  6. Look for a <dimension ../> tag (for example, <dimension ref='A1:C200' />).

If your file has a dimension attribute that points to a single cell (such as <dimension ref='A1' />), Power Query uses this attribute to find the starting row and column of the data on the sheet.

However, if your file has a dimension attribute that points to multiple cells (such as <dimension ref='A1:AJ45000'/>), Power Query uses this range to find the starting row and column as well as the ending row and column. If this range doesn't contain all the data on the sheet, some of the data won't be loaded.

How to fix incorrect dimensions

You can fix issues caused by incorrect dimensions by doing one of the following actions:

  • Open and resave the document in Excel. This action will overwrite the incorrect dimensions stored in the file with the correct value.

  • Ensure the tool that generated the Excel file is fixed to output the dimensions correctly.

  • Update your M query to ignore the incorrect dimensions. As of the December 2020 release of Power Query, Excel.Workbook now supports an InferSheetDimensions option. When true, this option will cause the function to ignore the dimensions stored in the Workbook and instead determine them by inspecting the data.

    Here's an example of how to provide this option:

    Excel.Workbook(File.Contents('C:MyExcelFile.xlsx'), [DelayTypes = true, InferSheetDimensions = true])

Sluggish or slow performance when loading Excel data

Microsoft Power Query For Excel Mac

Slow loading of Excel data can also be caused by incorrect dimensions. However, in this case, the slowness is caused by the dimensions being much larger than they need to be, rather than being too small. Overly large dimensions will cause Power Query to read a much larger amount of data from the Workbook than is actually needed.

Microsoft Power Query For Excel 2013

To fix this issue, you can refer to Locate and reset the last cell on a worksheet for detailed instructions.