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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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