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.
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,
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 |
---|---|
56 | |
27 | |
24 | |
14 | |
9 |
User | Count |
---|---|
77 | |
61 | |
47 | |
17 | |
12 |