Power Query is a user friendly ETL tool (Extract, Transform and Load). Traditionally ETL has been done using more complicated tools (such as SQL Server Integration Services – SSIS) and the resulting data is stored in a data mart or data warehouse for consumption by anyone that needs a standard view of the data. Power BI Desktop can consume tables directly from a data warehouse and simply load the table into Power BI – dead easy. But Power Query is also a powerful ETL tool in its own right, and it can be used to transform and reshape the source data directly inside Power BI Desktop (and then PowerBI.com). This is very useful if :
- You don’t have a data warehouse and/or
- You need some variation of what is in your data warehouse.
- You have other data sources that are not in a data warehouse but are still important to you.
Taking this approach (manipulate in Power Query) is perfectly fine if you have a single workbook, but what if you have 10 similar workbooks all needing the same transformation? Worse still, what if you are one of many people in a company all doing the same thing with multiple workbooks?
Read on for the solution.