Forum Discussion
How can I convert multiple tables into one table by using DAX code.
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") )
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!!
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")
)Thanks you so much for your help. it's working fine.
I have a different situation so I need your guidance how can I modify the DAX code.
I would like to pick certain of columns across 4 tables instead of picking entire tables because those 4 tables columns are not same and different lengths each tables. (Example - Table 1 contains 5 columns and Table 2 contains 10 columns and Table 3 contain 18 columns and Table 4 contain 22 columns)
Example of Desired Result each columns - id, city code, table references only across 4 tables.
Id that possible? Can you please advise.
yes, you can use SELECTCOLUMNS() as part of the DAX. This will only work if you know the column names for each of the tables, if the column types match, the columns are in the same order, and you have the same number of columns from each table. The UNION () will take the column names from the first table and ignore the column names from the other tables.
