Forum Discussion

jpmakako's avatar
jpmakako
Frequent Visitor
3 years ago
Solved

Power Query Nested Table Transformation

Hi, Im trying to replace null values in a nested table with the values form the outside table. Both in and out have the the columns [cta], [scta] and [sscta]. The idea is to replace any null value in...
Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    3 years ago

    McReplace = Table.FromRecords(Table.TransformRows(PreviousStep, each let a = [cta], b = [scta], c = [sscta] in _&[NestedTable=ModifyTable( [NestedTable] , a,b,c )]))

    or

    McReplace = Table.RelaceValue(PreviousStep, each ModifyTable( [NestedTable] , [cta],[scta],[sscta] ),"",(x,y,z)=>y,{"NestedTable"})

wdx223_Daniel's avatar
wdx223_Daniel
Icon for Community Champion rankCommunity Champion
3 years ago

McReplace = Table.FromRecords(Table.TransformRows(PreviousStep, each let a = [cta], b = [scta], c = [sscta] in _&[NestedTable=ModifyTable( [NestedTable] , a,b,c )]))

or

McReplace = Table.RelaceValue(PreviousStep, each ModifyTable( [NestedTable] , [cta],[scta],[sscta] ),"",(x,y,z)=>y,{"NestedTable"})

jpmakako's avatar
jpmakako
Frequent Visitor
3 years ago

Thanks, they both work. Care to explain to me what the "&" and "NestedTable=" inside the column is doing in the first statement and why is the each ModifyTable(...) in the old argument of the ReplaceValue function and "" in the new argument? Sounds counterintuitive to me.