• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Actual and Forecast from Different Tables

I am just getting started with Power Query. I have 3 tables:

Product ID
Actual Sales
Forecast Sales

Actual Sales has historical actual data by product by month. Lets say the data goes back 12 months. Forecast has monthly data by product going forward 12 months.

Product ID has the product name and additional details. Name is the unique key across all 3 tables.

How do I join the tables so that I can pivot and group dates such that I can see actual then forecast in series?
 
Load all tables as connection only.
Then click on merge (as new).
Join is done via selecting the matching columns.

If the purpose is having a data model on which you can pivot, the better alternative may be to use powerpivot (data model) and some DAX measures.
 
Yes--ultimately the intent is to pivot. I don't necessarily want to merge tables--just merge in a pivot so I can drop in a product and see actuals back 12 mos and forecast forward 12 mos.

Thanks for the response!
 
Don't merge tables if you can help it. Make sure that fact tables are kept separate from dimension tables.

Use One-to-Many relationship from dimension to fact table.
I.E. Star schema or Fact constellation schema.

Star schema - Fact Table connected to multiple dimensions.
63562

Fact Constellation schema - Collection of star schema (each dimension can connect to multiple fact tables)
In below sample, 3 dimension tables are connected to each of 4 fact tables. Making it collection of 4 star schema.
63563
 
Just to check back in--I believe I figured it out. I do need to merge tables. I have a set of products each with monthly forecast dates and separate production dates. Adding those separate date fields to a pivot table does not work as I had hoped.
 
Back
Top