From the course: Power BI Data Methods (2019)

Focus on Power Query

From the course: Power BI Data Methods (2019)

Start my 1-month free trial

Focus on Power Query

“

- Power BI has one of the most powerful semantic layers in its class which allows for many data shaping and calculation options. Power BI Desktop works as a composite model because it supports multiple data connections and sources. We can efficiently connect to data tables from multiple sources, such as both SQL Server and Excel files in a single application which we can then use for analysis. Let's focus on one aspect of the semantic layer. And that is, the capabilities of the Power Query Editor where the majority of the ETL process occurs. This Query Editor records both the data extraction and transformation steps for data connections. For those familiar with Excel VBA macros, the Power Query Editor similarly allows us to automate a repeated process for refreshing and updating data which saves having to repeat an entire process manually, and limits the opportunities for potential errors. We can easily transfer much of the material in this course to Power Query in Excel, allowing us to leverage this knowledge across multiple Microsoft products. Even using Power Query, Excel still has limitations in terms of size and performance for loading data and creating stable calculations. I would recommend giving tables sensible names such as, "Santa Barbara Weather." Rather than what we may see as the default query name. Many users who do not work in technical development roles can develop dashboards in Power BI. And using sensible table names, we can make working with the data easier and less intimidating for them. We can set up multiple data connections within the Power Query Editor. Including, data tables, lookup keys, and exception tables. Here we can connect the data table to the lookup key on the unique ID to link the region information to each country. This makes it easier to see and change the grouping without having to change the entire data table. Then, if we know we want to exclude France, for example, we create a separate exceptions table to manage whether or not we include it in the data set by essentially filtering it out.

Contents