Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I dont find the formula for my problem . hopefully somebody can help me.
i have different tables with different fields, but 3 columns are always the same . this 3 columns i want to have in a addional table ( distinct).
Solved! Go to Solution.
Try like
distinct(
union
(
selectcolumns(Table1,"UPI",Table1[UPI],"ID",Table1[ID],"Personalnumber",Table1[Personalnumber]),
selectcolumns(Table2,"UPI",Table2[UPI],"ID",Table2[ID],"Personalnumber",Table2[Personalnumber]),
selectcolumns(Table3,"UPI",Table3[UPI],"ID",Table3[ID],"Personalnumber",Table3[Personalnumber])
)
)
You can also use summarize in place of select columns, Small diff in syntax.
I tried the DAX solution and it is working fantastic. Exactly what I was looking for. I had the Distinct and Union command in my mind and tried, but the selectcolumns i didn't think off.
Thanks very much for the fast response. It saved my day . 🙂
HI @Sonne ,
You can achieve that in the Power Query Editor with the tools available in the Ribbon.
You can use the Append Queries function in te Combine group of the Home tab.
Then you Choose Columns (Manage Columns group in the Home tab) you want to keep and Remove Duplicates (Click on Remove Rows in the Reduce Rows group of the Home tab)
Let us know if that works for you.
Regards
David
Try like
distinct(
union
(
selectcolumns(Table1,"UPI",Table1[UPI],"ID",Table1[ID],"Personalnumber",Table1[Personalnumber]),
selectcolumns(Table2,"UPI",Table2[UPI],"ID",Table2[ID],"Personalnumber",Table2[Personalnumber]),
selectcolumns(Table3,"UPI",Table3[UPI],"ID",Table3[ID],"Personalnumber",Table3[Personalnumber])
)
)
You can also use summarize in place of select columns, Small diff in syntax.
@Sonne What @amitchandak is proposing is a DAX measure. Just precision in case you are not entirely familiar.
So now you have a solution in both languages M (Power Query) and DAX.
Your choice.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
78 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |