Combining 5 tables into one based on a condition where a column value equal to certain text

[2019]

I have a challenge which I tried to accomplish but without any success,

I want to combine 5 tables (see below)

Into new one table where I select which column to bring (see below)

But under one condition if a certain column value equal to certain text (see below)

Apprecite your help whether it should be done in DAX or Power Query M

link to PBIX file   and XLSX file 

[amitchandak · Accepted solution]

@2019 

I have not to check the file , but it should happen like

https://docs.microsoft.com/en-us/dax/selectcolumns-function-dax
union (
selectcolumns(filter(Table,<condition>), columns....),
selectcolumns(filter(Table2,<condition>), columns....),
selectcolumns(filter(Table3,<condition>), columns....),
selectcolumns(filter(Table4,<condition>), columns....),
selectcolumns(filter(Table5,<condition>), columns....)
)

You can use summarize in place of select column

You can use distinct on top of union if needed

Also refer : https://radacad.com/append-vs-merge-in-power-bi-and-power-query