Joint a measure column and form a new table

[NickProp28]

Dear Community,

I have two table, Client and Teu tables

Client table

TEU table

I want to join the client and TEU to a new table

Taking ConsolNumber and Unique Match Column from Client table, remove duplicate. And then join to TEU table all columns.

At the end, I have ConsolNumber, UniqueMatchColumn, TEU and ETD in a new table.

I knew that there have append queries option in query editor, but since column unique match is a measure column. So I cant use this option. 

Here is the example pbix: https://ufile.io/mppry8cy

Appreciate any helps and thank for your attention!

[amitchandak · Accepted solution]

@NickProp28 , first create a copy of the table 1, remove columns other then ConsolNumber and Unique Match Column

delete duplicates. Them merge with table 2

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

Create a table from the table and delete duplicate

https://www.youtube.com/watch?v=kU2M1LmNvNo

https://www.youtube.com/watch?v=vHuhbvYCiNc

[NickProp28]

Hi @amitchandak ,

Thanks for the reply. I know there have merge option in query editor, but Unique Match is a measure column. So it do not show in the power query editor. 

[amitchandak]

@NickProp28 , Then you have to it DAX.

Summarize(Table, Table[ConsolNumber], "Unique Match" , [Unique Match] )

Then cross join with filter

examples

filter(crossjoin(Table2, Summarize(Table1, Table1[ConsolNumber], "Unique Match" , [Unique Match] )), [ConsolNumber] =[ConsolID]) //add more in filter if needed

natural inner and other option explore

https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

[NickProp28]

Dear @amitchandak ,

Thanks for the guide. I have this new table

But I'm not allow select the specific table column for table 2. I do not want another ConsolID show in this new table, 
I tried this DAX, but fail to excute.

Newtable = FILTER(CROSSJOIN(SUMMARIZE(TEU,TEU[ETD],TEU[TEU]),Summarize(Client, Client[ConsolNumber], Client[Unique Match Column] )),Client[ConsolNumber]= TEU[ConsolID])

Kindly advice