Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi,
How is it possible to summarize together 2 different tables, excluding some values based on the results of a column?
For the following scenario:
I have a Table1 with unique IDs:
table1 |
ID |
1 |
2 |
3 |
4 |
5 |
6 |
There is also this Table2 with some IDs from Table1:
table2 | |
ID | reason |
1 | conclude |
1 | steady |
1 | not okay |
2 | steady |
2 | conclude |
3 | conclude |
6 | away |
I would like to combine both tables into a Table3, where I have all ID's from Table1, and all the reasons from Table2, excluding the reason "conclude", also, when there is no reason for the IDs, the result will be "null":
note: for ID 3, there is only "conclude", so the result would be null, because I need to exclude when the reason is "conclude"
table3 | |
ID | reason |
1 | steady |
1 | not okay |
2 | steady |
3 | null |
4 | null |
5 | null |
6 | away |
I have thousands of reasons, so it would be easier to have a filter that excludes only when the reason is <> "conclude".
Thx!
Solved! Go to Solution.
Hi @Anonymous
I hope this works. Otherwise, we can also use GENERATE function.
Table3 =
NATURALLEFTOUTERJOIN (
SELECTCOLUMNS ( Table1, "ID", Table1[ID] & "" ),
SELECTCOLUMNS (
FILTER ( Table2, Table2[Reason] <> "Conclude" ),
"ID", Table2[ID] & "",
"Reason", Table2[Reason]
)
)
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
Hi @Anonymous
I hope this works. Otherwise, we can also use GENERATE function.
Table3 =
NATURALLEFTOUTERJOIN (
SELECTCOLUMNS ( Table1, "ID", Table1[ID] & "" ),
SELECTCOLUMNS (
FILTER ( Table2, Table2[Reason] <> "Conclude" ),
"ID", Table2[ID] & "",
"Reason", Table2[Reason]
)
)
Hello,
In Power Query select 'Merge Queries As New' to create you Table3. Before you Left Outer join them together remove "conclude" from Table2. The Results will give you the following once you expand the Table2 Results.
Results:
Thank you,
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
23 | |
21 | |
20 | |
13 | |
12 |
User | Count |
---|---|
43 | |
31 | |
24 | |
23 | |
22 |