This week I had a situation where I need to create a budget table in Power Pivot. I had the option to upload a weekly budget as a single record per store and using DAX to calculate year-to-date budgets, or load a recordset (one for each week) in a budget table. I chose the latter in this case as gave me the opportunity to test new skills in Power Query. I have recreated the scenario below.
Weekly budgets per 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 data but not optimal for Power Pivot. In my real life example, there were many more columns for Contraction, GP and some other metrics on the page.
Long, narrow boards are usually better for Power Pivot, so I used Power Query to reshape this table to look like the following, using a simple Power Query non-pivot column transform.
Once this table was created in Power Query (import from spreadsheet then transform), I called the consultation “Budget” and I configured it as “just create connection”.
This makes it available for use in other queries. without storing the data anywhere in Excel. Every time it 'materializes’ a query by adding it as a table in Excel or Power Pivot, it takes space. Only do this if you need the materialized view of the table.
Identification numbers of the week
I needed to set the budget each week for the next 15 weeks. I was using a weekly calendar in this example (not a daily calendar), so I had week ID as below in YYWW format. Note the non-contiguous jump at the end of the calendar year in 2016.
Create the week ID list in Power Query
This was my first chance to learn a new skill... I knew it was possible to create a native list in Power Query instead of importing the list from Excel (i read this somewhere, probably some of Chris Webb) but i wasn't quite sure how. I googled and tried List.Generate () but it was wrong. Later, a flashback: all you had to do was use the following syntax with curly braces.
= {1..5}
The above generates a list of numbers from the 1 al 5. With this in mind, i did the following:
- I created a new query by executing the following menu steps:
- Energy query
- From other sources
- Blank query
- Query name changed to “Weeks”.
- I wrote a line of code like this in the formula bar = {1545..1552,1601..1607} and gave me exactly what I needed. I didn't know the comma syntax would work to create non-contiguous ranges when I wrote the formula, but it worked. After you get some practice and experience with a new language like this, you start to see the patterns in the syntax and you get free kicks like this. (Note: if you can't see the formula bar, activate it from the View menu)
- Note that at this point is “solo” a list, not a table. To convert the list to a table, I clicked Transform to Table, then i gave the new table a column name.
- I also saved the query as “just create connection” so that the table would not materialize, I just created the instructions on how to create the table in a new query.
At this point I was very happy and impressed with my new skills., but there were still problems ahead. The next step was the cross joining of the tables.
Cross join the two tables
The weeks table above has 15 rows and the budget table has 10 rows. When I Say “cross combination”, what i mean is i need to duplicate the entire budget table for each week, adding week column in budget table. So I need something as shown below, but for every possible combination. namely, 15 x 10 = 150 rows in my new table.
I quickly googled Cross Join Power Query and found some references to Full Outer Joins, but this is not what i needed. Then i had an idea: maybe you could write a dummy function to simulate the Cross Join process. I tried this and it worked fine.
Edit 31/10/15
After posting, I got some good suggestions from Imke Feldman; it seems the function is not needed at all; actually makes sense, but I hadn't realized. Chris Webb also published a more effective approach (which is important if you have large boards), so here is the updated approach.
Add a custom column to create a cross join
- Open the Weeks table and add a new custom column (shown below). The formula for the new column is simply the name of the Budget Query.
- After clicking OK, gets a new column that “contains” the complete Budget table as an object in each row of the Weeks table (as it's shown in the following).
- All you had to do then was expand the new column by clicking the expand button (shown above) to create all possible combinations (shown below)
I configured the data types of the numeric columns and loaded the table directly into my data model so that I can use it in Power Pivot.
You can also read Chris Webb's suggestion in the comments below)
