Forum Discussion

topazz11's avatar
topazz11
Helper III
2 years ago
Solved

Combining multiple table with date

Hello,

 

I have 3 tables which contains similar data with month/year and some blank/zero value.

I'd like to combine them as single table.

 

What is the best way to achive this?

 

Thanks,

 

 

desired output

 

Need Help
  • vicky_'s avatar
    vicky_
    2 years ago

    You can either merge queries (2x) in PowerQuery, or you can try create a table with DAX. Something like:
    CombinedTable = ADDCOLUMNS(Table1, "Actual1", RELATED(Table2[Actual1]), "Actual2", RELATED(Table3[Actual2]))

     

    (the above dax would only work if there's a relationship between the date columns)

2 Replies

  • vicky_'s avatar
    vicky_
    Super User
    2 years ago

    You can either merge queries (2x) in PowerQuery, or you can try create a table with DAX. Something like:
    CombinedTable = ADDCOLUMNS(Table1, "Actual1", RELATED(Table2[Actual1]), "Actual2", RELATED(Table3[Actual2]))

     

    (the above dax would only work if there's a relationship between the date columns)

  • MattAllington's avatar
    MattAllington
    Community Champion
    2 years ago

    Using power query, load each of the 3 tables and set so they don't load

    modify each table so it has a "type" column.  Ie the first table will be type = Plan, second will be Actual1, etc. you do this by adding a custom column and set the value of the column to the type

    rename the data column to be called "value". All 3 tables must have exactly the same column names

    append the 3 tables together and load. 

    you can then build the output you want from this final loaded table.