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
ale-hd
Helper I
Helper I

Conditional merge in query editor

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

Table1.JPG

 

TABLE 2

Table2.JPG

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

Table Merged.JPG

 

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

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

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"

View solution in original post

1 REPLY 1
lbendlin
Super User
Super User

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"

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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