Power Query - What A Tool!
Recently, I worked on a report for the rental business at my company. The report has a column chart that displays the number of units in a variety of statuses. To validate the accuracy of my report, I compare my results to that of the source system. To do that, I obtain an extract from the source system (Excel file) and compare that to an extract from Power BI (.csv file).
Originally, I performed this comparison manually - copied data from one file into a separate Excel file and then used functions like VLOOKUP to try and find the differences. It kinda worked, but not always. Even if it showed the differences it wouldn't show any patterns to explain the cause of the difference. Additionally, the process was tedious, time consuming and not always accurate.
This week I had the idea to try and use Power Query to automate the data wrangling and prep. I placed my extracts into a folder and used Power Query to read and transform the data from each file into a consistent table layout (same column names in the same order). From there I appended the queries and created a master table of the combined data. Finally, I Close and Load to a PivotTable report and bingo! I can quickly find the differences AND see the patterns that explain the differences. Cool!
Now with this Power Query approach I can repeat or update the analysis in seconds vs. hours because Power Query does all the data wrangling and prep almost instantly. Now I can spend my time on the analysis. I accomplished at least 10x more in a single week than I would have previously. Additionally, I get to the insights so much more rapidly and I can easily explain them to my business partners. Since my analysis is so much better and faster, my business partners have much more confidence in the Power BI report. That's a big win!
Power Query is such a great tool - and everyone has access to it! I'm a little embarrassed it took me this long to realize I could use Power Query this way. I know I'm now a better Analyst and more valuable to my business partners.
