Conditional merge in query editor

Question

Hi all, I am stucked in something... extremely difficult to me.

Here you can download the (very small) pbix file

Have 2 tables, "table 2" has unique values in column "ID 2".

TABLE 1

TABLE 2

When "ID 2" is matching I need to obtain in Table 1 all of the corresponding columns from Table 2.

This is basic and easy. BUT in table 1 there are columns where "ID 2" is empty, therefore, as exptected, I obtain "null" values when this happens.

MERGED TABLE: TABLE 1 GETS VALUES FROM TABLE 2 WHEN "ID 2" IS MATCHING

What I need is:

when "ID 2" in Table 1 is empty I need the cells with "null" values, being populated with the values from the first row (from up to down) with matching "ID 1" in table 2.

So, if I could write an "If" statement I would say:

If "ID 2" in "Table 1" is not empty than merge all the columns from the "ID 2" matching rows in "Table 2". Otherwise merge all the columns from the first row met, reading from up to down, matching the "ID 1".

In the exampe I should obtain "Good - New york - Usa" in lines 3 and 4; "Any , Sadsfas , fdd... " in line 8

Please note: for some reason I necesseraly have to manage this in Query Editor.

Thank you so much for your help, Alessandro

lbendlin · Accepted Answer

You can iterate through the table (you have to do it for each column) and replace null with the column value in the first row
 
[column] {0}
 
Here is the first step to do that - you still need to add the section logic.
 
let
    Source = Table.NestedJoin(#"Table 1", {"ID 2"}, #"Table 2", {"ID 2 "}, "Table 2", JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Source, "Table 2", {"DETAIL 1", "DETAIL 2 ", "DETAIL 3"}),
    #"Replaced Value" = Table.ReplaceValue(Expand,"",Expand[ID 2]{0},Replacer.ReplaceValue,{"ID 2"})
in
    #"Replaced Value"

Forum Discussion

Conditional merge in query editor

1 Reply

Related content

Conditional Merge

Conditional merge columns in Query Editor

MERGE QUERIES

Merge columns based on condition

Async merge risk