Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anonymous
Not applicable

Summarize 2 different tables with filters and returning null for empty results

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 
IDreason
1conclude
1steady
1not okay
2steady
2conclude
3conclude
6away


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 
IDreason
1steady
1not okay
2steady
3null
4null
5null
6away


I have thousands of reasons, so it would be easier to have a filter that excludes only when the reason is <> "conclude".

Thx!

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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]
)
)

View solution in original post

3 REPLIES 3
ThxAlot
Super User
Super User

ThxAlot_0-1695235130525.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



tamerj1
Super User
Super User

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]
)
)

SQL_SousChef
Frequent Visitor

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:

SQL_SousChef_1-1695226312273.png

 

Thank you,

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.