Power Query - Combine Different tables into one

  • Hi,


    I have three tabs in my file with the same headings, but the "Amount" represent different things - Current Year Actual/Budget/PY figures. I was trying to combine them into one using Power Query (using "Combined" column) - so that I have a table with all the same heading plus a different column for each "Amount", so I could then later do variance analysis using Pivot Table (Summary Tab).


    For whatever reason it didn't want to work, would anyone in this forum have any suggestions how this could be achieved? Either Power Query or any other method would be much appreciated.


    Thanks,

    Jurate

  • Hi,


    Attached is your test file with a starting point ... tables combined into one with Power Query ;)


    Hope this will help

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Thanks Carim,


    This is very useful, but I was hoping then to be able to have a calculated field to get the variances, for example "Actual VS Budget"= Actual -Budget


    Do you know any way around that please?


    Thanks,

    Jurate

  • Hi Alan,


    Would you be so kind to show it on my sample data? I have tried it, but was not sure how to do it. "Combination" table only has one field "Amount" to choose from - Actual/Budget/PY does not come as separate fields in Power Pivot either?


    Thanks,

    Jurate

  • see file. Created new columns in PQ for PY, Actual, Budget. Built PT in Power Pivot and created Measure to show Budget vs Actual

  • You can also create measures for each type in PP using formulas like:


    =CALCULATE(SUM(Combined[Amount]),Combined[Name]="Actual")


    for Actuals and so on. Then you can do whatever other calculations you need with those measures.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why