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

[Anonymous]

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!

[ThxAlot]

[tamerj1 · Accepted 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]
)
)

[SQL_SousChef]

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,