Forum Discussion

Astrua's avatar
Astrua
Regular Visitor
2 years ago
Solved

Finding the Delta between 2 nested tables

Hi All,   I have a list of data with 2 nested tables with before and after process data. How do I write the code to find the delta between the before and after table? row 1 and column 1 of [after] ...
Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSRCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "before", each Before),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "after", each After),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Column1", each [after]{[ID]-1}[Column1]-[before]{[ID]-1}[Column1]),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Column2", each [after]{[ID]-1}[Column2]-[before]{[ID]-1}[Column2]),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Column3", each [after]{[ID]-1}[Column3]-[before]{[ID]-1}[Column3]),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Column4", each [after]{[ID]-1}[Column4]-[before]{[ID]-1}[Column4])
in
    #"Added Custom5"

     

     

     

lbendlin's avatar
lbendlin
Super User
2 years ago

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSRCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "before", each Before),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "after", each After),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Column1", each [after]{[ID]-1}[Column1]-[before]{[ID]-1}[Column1]),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Column2", each [after]{[ID]-1}[Column2]-[before]{[ID]-1}[Column2]),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Column3", each [after]{[ID]-1}[Column3]-[before]{[ID]-1}[Column3]),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Column4", each [after]{[ID]-1}[Column4]-[before]{[ID]-1}[Column4])
in
    #"Added Custom5"