The capabilities of the Power Platform wowed many organizations. Especially Power BI and its ability to extract, transform, and load (ETL) datasets from dozens of disparate data sources. At the same time, Microsoft Dataverse, the low-code data storage offering of the Power Platform, also supports ETL of disparate data sources. Though, this isn’t unique to Power BI or Microsoft Dataverse. Power Query, the underlying engine powering this ETL process, has been a part of Microsoft Excel for a while now.
“Power Query is a data transformation and data preparation engine.” Meaning, it is capable of selecting (extract) data from almost any data source, then formatting (transform) the data into a useful format before everything is finally imported (load) into Excel. People are likely comfortable importing CSVs and text files into Excel, but Power Query can actually import data from web pages, pictures, PDFs, and more. Select the Get Data button from the Data menu in the ribbon and skim the available data options:
Assuming a dataset needs to be transformed before its properly loaded into Excel, Power BI or Dataverse, Power Query offers plenty of ways to work with the data. From the Home tab in the ribbon, we can select and remove rows and/ or columns or split existing columns into multiple columns. Important to note, these data changes won’t impact the original source data in any way:
Data transformation is sometimes called “cleaning” because the data is at times unusable in its current format. For instance, column types occasionally need to be corrected. Power Query may incorrectly assume some column data are strings when they’re actually dates. Or rather, we may just need to rename a column to make it more descriptive. Better yet, we may need to replace values in our data columns like eliminating nulls:
Also, it’s possible to dynamically add columns to the dataset. Imagine importing records of people with their first and last names in separate columns. Here, we could choose to concatenate those columns into a “Full Name” column. What’s more, a custom column or two could be added with dynamic logic using the importing data values:
Conclusion:
Power Query is Microsoft’s data engine to extract, transform, and load (ETL) data into Microsoft Excel, Power BI, and Microsoft Dataverse. Data transformations are only reflected in the importing system and won’t impact the source system at all. Transform the data as necessary without consequence…
“If you have no confidence in self, you are twice defeated in the race of life.”
Marcus Garvey
#BlackLivesMatter
console.log('Charles'); 