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
GermanAndres
Frequent Visitor

How to replace only existing values in join? Is there a better way?

So here's the problem, I have two datasets, one has a lot of data, but with some outdated fields and the other has some specific columns with updated data. The nomber of columns is rather arbitrary, but they always have the same name. Due to the sensitivity of the data I'm going to post dummy data.

 

Assumptions:

  • The values to replace have the same column name
  • There are no duplicate dates, so when there's a join the table value only has one record
  • There are no nulls on the new table
  • On the original table, there will be columns that are not in the new table
  • On the original table, there will be more rows that don't join with the new table

 

Outdated table:

old.png

Table with new data:

image.png

What I expect as output:

GermanAndres_0-1689453559722.png

 

What I am currently doing:

  1. Start with the bigger older dataset
  2. Left join with the second updated dataset on the same date
  3. Because the second dataset is smaller, there's a lot of nulls in there. I create a custom column where I check if the new value is null, if it is, pick the value from the first table, if it isn't pick the value from the new table.
  4. Delete the first two columns, rename the latter to the original name

 

 

let
    Source = OldData,
    MergeWithNewData = Table.NestedJoin(Source, {"Date"}, NewData, {"Date"}, "NewData", JoinKind.LeftOuter),
    ExpandNewData = Table.ExpandTableColumn(MergeWithNewData, "NewData", {"L1"}, {"NewData.L1"}),
    AddNewColumn = Table.AddColumn(ExpandNewData, "New_L1", each if [NewData.L1] = null then [L1] else [NewData.L1], Int64.Type),
    DeleteOldColumns = Table.RemoveColumns(AddNewColumn,{"L1", "NewData.L1"}),
    RenameNewColumn = Table.RenameColumns(DeleteOldColumns,{{"New_L1", "L1"}})
in
    RenameNewColumn

 

 

My problem is that this approach, although it works, can get REALLY messy when there's a lot of columns. So, is there a better way?

 

Thanks in advance

1 ACCEPTED SOLUTION
v-yanjiang-msft
Community Support
Community Support

Hi @GermanAndres ,

According to your description, here's my solution. Add a step in advanced editor of the Outdated table:

#"Replace Value" = Table.ReplaceValue(#"Changed Type",each [L1], each try Table.SelectRows(#"Table",(x)=>x[Date]=[Date])[L1]{0} otherwise [L1], Replacer.ReplaceValue,{"L1"})

#"Table" in the formula is the table with new data. Get the correct result:

vyanjiangmsft_0-1689661533133.png

I attach my sample below for your reference.

 

Best regards,

Community Support Team_yanjiang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-yanjiang-msft
Community Support
Community Support

Hi @GermanAndres ,

According to your description, here's my solution. Add a step in advanced editor of the Outdated table:

#"Replace Value" = Table.ReplaceValue(#"Changed Type",each [L1], each try Table.SelectRows(#"Table",(x)=>x[Date]=[Date])[L1]{0} otherwise [L1], Replacer.ReplaceValue,{"L1"})

#"Table" in the formula is the table with new data. Get the correct result:

vyanjiangmsft_0-1689661533133.png

I attach my sample below for your reference.

 

Best regards,

Community Support Team_yanjiang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

This is a really clean solution, thank you!

gregoliveira
Helper II
Helper II

Hi.

I believe you can use some of the list functions to solve this scenario. Something like:

 

  1. Transform columns into lists.
  2. Transform lists to a single list of lists using List.Zip for each row.
  3. Transform the list of lists using List.Transform and List.RemoveNulls.
  4.  Transform the list into columns.

Hope this help you!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 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.

Top Solution Authors