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

[Saxon10]

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]

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]

Thanks for your reply and help. 

[lbendlin · Accepted solution]

@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")
)

[Saxon10]

@lbendlin,

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. 

[lbendlin]

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. 

[Saxon10]

Thanks you so much for your help. it's working fine.