Forum Discussion
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 the tables data into one table based on the filter column status is “No” only also I want each table reference end of the result so I know which table data.
I am unable to use the power query because some of the columns are not part of the original data and It’s came from another table by using DAX code.
Data:
TABLE1 | |||
Id | Result | Filter | City code |
133 | win | OK | SRH |
134 | loss | OK | SRH |
135 | loss | OK | SRH |
136 | tie | OK | SRH |
137 | win | NO | DEL |
138 | loss | NO | DEL |
139 | tie | NO | DEL |
140 | win | NO | DEL |
141 | win | NO | DEL |
142 | win | NO | DEL |
TABLE2 | |||
Id | Result | Filter | City code |
123 | Win | NO | MI |
124 | loss | NO | MI |
125 | tie | NO | MI |
126 | win | NO | MI |
127 | win | NO | MI |
128 | win | OK | MI |
129 | loss | OK | MI |
TABLE3 | |||
Id | Result | Filter | City code |
123 | Win | OK | MI |
124 | loss | OK | MI |
128 | win | NO | MI |
129 | loss | NO | MI |
130 | tie | NO | SRH |
131 | tie | NO | SRH |
132 | win | NO | SRH |
TABLE4 | |||
Id | Result | Filter | City code |
123 | Win | OK | MI |
124 | loss | OK | MI |
125 | tie | OK | MI |
126 | win | OK | MI |
127 | win | OK | MI |
128 | win | OK | MI |
134 | loss | NO | SRH |
135 | loss | NO | SRH |
136 | tie | NO | SRH |
137 | win | NO | DEL |
138 | loss | OK | DEL |
139 | tie | OK | DEL |
140 | win | OK | DEL |
141 | win | OK | DEL |
142 | win | OK | DEL |
Desired Result:
RESULT | ||||
Id | Result | Fillter1 | City code | Table Reference |
137 | win | NO | DEL | Table1 |
138 | loss | NO | DEL | Table1 |
139 | tie | NO | DEL | Table1 |
140 | win | NO | DEL | Table1 |
141 | win | NO | DEL | Table1 |
142 | win | NO | DEL | Table1 |
123 | Win | NO | MI | Table2 |
124 | loss | NO | MI | Table2 |
125 | tie | NO | MI | Table2 |
126 | win | NO | MI | Table2 |
127 | win | NO | MI | Table2 |
128 | win | NO | MI | Table3 |
129 | loss | NO | MI | Table3 |
130 | tie | NO | SRH | Table3 |
131 | tie | NO | SRH | Table3 |
132 | win | NO | SRH | Table3 |
133 | win | NO | SRH | Table4 |
134 | loss | NO | SRH | Table4 |
135 | loss | NO | SRH | Table4 |
136 | tie | NO | SRH | Table4 |
137 | win | NO | DEL | Table4 |
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") )
6 Replies
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.
Thanks for your reply and help.
