Forum Discussion

Saxon10's avatar
Saxon10
Icon for Post Prodigy rankPost Prodigy
4 years ago
Solved

How can I convert multiple tables into one table by using DAX code.

  I have 4 tables are Table1,2,3 and table4 and it’s contain the following columns are id, result, filter and city code. The tables headers are same but the data is different.   How can I get all ...
Need Help
  • lbendlin's avatar
    lbendlin
    4 years ago

    jeroendekk you can do that with ADDCOLUMNS().

     

    Personally I would apply the filter earlier, but it may not matter.

     

    Combined Table = UNION (
ADDCOLUMNS(FILTER(Table1,[Filter]="No"),"Table Reference","Table1"),
ADDCOLUMNS(FILTER(Table2,[Filter]="No"),"Table Reference","Table2"),
ADDCOLUMNS(FILTER(Table3,[Filter]="No"),"Table Reference","Table3"),
ADDCOLUMNS(FILTER(Table4,[Filter]="No"),"Table Reference","Table4")
)
jeroendekk's avatar
jeroendekk
Icon for Responsive Resident rankResponsive Resident
4 years ago

Hi Saxon10 
I don't know a dynamic way to get the the table name in a column using DAX. But you could add it in each table using DAX by creating a calculated column in each of the tables.

 

Table = "Table1"

 


Then you could append and filter the tables with a FILTER and UNION formula.

 

Combined Table =
FILTER ( UNION ( Table1, Table2, Table3, Table4 ), [Filter] = "NO" )

 

As you probably know, this is not a very efficient way to append tables (as all data will be double in the datamodel). I personally would try to see if the transformations you did in DAX to create the table(s) are possible in Power Query. 

Best regards,
Jeroen

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!




Saxon10's avatar
Saxon10
Icon for Post Prodigy rankPost Prodigy
4 years ago

Thanks for your reply and help.