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

[GermanAndres]

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:

Table with new data:

What I expect as output:

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

[v-yanjiang-msft · Accepted solution]

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:

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.

[GermanAndres]

This is a really clean solution, thank you!

[gregoliveira]

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!