Power Query: Hot Cross Joins
25 April 2018
Welcome to our Power Query blog. This week I look at cross joins.
I have a list of expenses that my fictional salespeople have incurred in a month. I’d like to expand this to estimate what they might spend in the next 10 months.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/04-apr/pq-73-image1.png/98fa3f68aa8137c123783b16652f856b.jpg)
I start by creating a query from my expenses data, by choosing ‘From Table’ in the ‘Get and Transform’ section of the ‘Data Tab’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/04-apr/pq-73-image2.png/e54f6d8bb18cbc09cc3f1e37ea23962d.jpg)
I confirm where my data is, and that the first line contains the headers. It doesn’t matter that my data is not currently in a table: Power Query will convert it to a table as part of the process.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/04-apr/pq-73-image3.png/32ad31c0b911087918757a74eb79ddd4.jpg)
My query is shown below. I call it ‘Expense_Forecast’ and save it as connection only.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/04-apr/pq-73-image4.png/3011f276822356ec26c43adffe97d230.jpg)
Now I want to create a list of months – this is easy enough to do by creating a new blank query from the ‘New Query’ option in the ‘Get and Transform’ section of the ‘Data’ tab, viz.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/04-apr/pq-73-image5.png/5069b27d76dd22df5d156e9dde2375ec.jpg)
I create a list using basic list functionality (more on this may be found in Power Query: Birthday Lists.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/04-apr/pq-73-image6.png/ab7b6e071bdb1ac217714ebc4e8b1ceb.jpg)
The formula I have used is
= {“0518”, “0618”, “0718”, “0818”, “0918”, “1018”, “1118”, “1218”, “0119”, “0219”}
In order to link this list to my ‘Expense_Forecast’ table, I want this to be a table too, so I convert it using the ‘To Table’ option in the ‘Convert’ section of the ‘Transform Tab’. I rename my column and call the query ‘Month_Numbers’. I save this query as connection only too.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/04-apr/pq-73-image7.png/c3a6370f2d1e61c31597a9e291ca4f56.jpg)
So now I need to combine my information. I decide to do this by adding the expense information to my ‘Week_Numbers’ query. I can do this by going to the ‘Add Column’ tab. I choose to add a ‘Custom Column’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/04-apr/pq-73-image8.png/6a0e9c79bf70b5e555fad54f97bd91f8.jpg)
If I expand the ‘Queries’ pane to the left of my screen, I can use the name of my other query in the column.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/04-apr/pq-73-image9.png/ee367d46e57a67c24c9469641cb2213f.jpg)
This will create a new column for my ‘Expense_Forecast’ data.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/04-apr/pq-73-image10.png/c7a1f266a50cf432b9aaeb3715f697e7.jpg)
I can now expand my column to show the data in the table – in this case I will choose all the available data. All I need to change, is to untick the ‘Use original column name as prefix’ option (no, I don’t know why it’s the default either!).
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/04-apr/pq-73-image11.png/90a8ff8d639fd8e44a886d5fe2aaea27.jpg)
I can then see the basic expense forecast for each employee and category for the next 10 months. I can then upload this data to Excel or PowerPivot and further investigate with the effects of inflation and predicted workloads.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/04-apr/pq-73-image12.png/5ee45d9c45be135ab97666cc8ebeaf05.jpg)
Want to read more about Power Query? A complete list of all our Power Query blogs can be found here. Come back next time for more ways to use Power Query!