Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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 , 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
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.
@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/
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
User | Count |
---|---|
96 | |
87 | |
78 | |
72 | |
68 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |