Adding column values from other table for empty cells

[l1800turbo]

Hello,

I'm building a table where I fetch data from an SQL database. In this table some values are empty (=null).

For these empty cells I want to add values from another table, but they should only be inserted on empty cells.

My current approach after hours was iterating over all columns that can contain cells with possible null values to be replaced and running a small function. In the function I replace all empty values with the values of the other table.

This is my current idea, it's working, but terribly slow and takes minutes to process.

Replacer = (TableRef as table, ColumnName) =>
        Table.ReplaceValue(
        TableRef,
        each null,
        each
            let
                currentMatNr = [MatNr]
            in
                List.First(
                    Table.Column(
                        Table.SelectRows(Simulation_Database_TSA_tmp, each [MatNr] = currentMatNr),
                        ColumnName
                    )
                ),
        Replacer.ReplaceValue,
        {ColumnName}
    ),

TSA_Columns = {"H", "TAMAX", "TJMAX", "RTHJA"},

TSA_DB_Add3=List.Accumulate(TSA_Columns, TSA_DB_Add2, (param_seed, param_list)=>Replacer(param_seed, param_list)),

I'm quite sure there are much better ways, maybe someone has an idea for this.

Thanks!

[l1800turbo]

Hello,

thank you for the answers!

@BA_Pete : Unfortunately one is SQL and the other table is Excel from a Sharepoint. Otherwise a direct SQL Query would be the best, for sure!

@wdx223_Daniel : Could you explain the function a little bit? MatNr is a unique column.

I tried your proposal, but I got an error, especially the part

a{[MatNr=[MatNr]]}? ??[]

 is unclear to me. PowerQuery marks a as unknown, I don't know this kind of condition to look up the meaning. At first it looked like a short IF like in C, but I couldn't find anything similar, so far.

Thank you!

[wdx223_Daniel]

if the table of Simulation_Database_TSA_tmp is unique in the column of MatNr, then

TSA_DB_Add3=let a=Table.Buffer(Simulation_Database_TSA_tmp) in Table.FromRecords(Table.TrandformRows(TSA_DB_Add2,each Record.TransformFields(_,List.Transform(TSA_Columns,(x)=>{x, each Record.FieldOrDefault(a{[MatNr=[MatNr]]}? ??[],x,null)}),2)))

if not, then

TSA_DB_Add3=let a=Table.Buffer(Table.Group(Simulation_Database_TSA_tmp,"MatNr",{"n",each _{0}})) in Table.FromRecords(Table.TrandformRows(TSA_DB_Add2,each Record.TransformFields(_,List.Transform(TSA_Columns,(x)=>{x, each Record.FieldOrDefault(a{[MatNr=[MatNr]]}?[n]? ??[],x,null)}),2)))

[BA_Pete]

Hi @l1800turbo ,

Is the table that you want to get the replacement values from on the same SQL server/database as the one with the nulls? If it is then it would be significantly faster to just merge the two tables and create new conditional columns as

if [originalTableCol] = null then [otherTableCol] else [originalTableCol]

This would fold back to the server so would be processed in a heartbeat.

Even if you can't get it to fold, I'm thinking this method could be quicker than your custom function anyway.

Pete