Power Query is still very new, so there is a lot to learn. I like to mix my learning up by doing some formal learning (such as reading a book like M is for Data Monkey) as well as “on the job” learning. What I mean by “on the job” learning is when I go out of my way to use the software I am trying to learn in small, manageable but meaningful new ways. You need to take the opportunity to solve real world problems in your real job if you are to ever build your skills.
I had a situation this week where I need to create a budget table in Power Pivot. I had a choice of either loading a weekly budget as a single record per store and using DAX to calculate the Year To Date budgets, or load a set of records (one for each week) in a budget table. I chose the latter in this case as it gave me a chance to try out some new skills in Power Query. I have recreated the scenario below.
Weekly Budgets by Store
I have a spreadsheet that contains the budget per store per week. The budget is the same for each week for each store for the next 15 weeks. The budget table looks like this – easy to enter the data but not optimal for Power Pivot. In my real life example, there were many more columns for Shrinkage, GP and some other metrics across the page.
Long narrow tables are normally better for Power Pivot, so I used Power Query to reshape this table so it looks like the one below – using a simple un-pivot columns transformation from Power Query.