Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Add difference column based on dynamic column names

Hello, I have the following table in Power Query. The 2nd and 3rd column names with dates are dynamic - the dates change every week. I am trying to add a Difference column but it should take into a...
Need Help
  • CNENFRNL's avatar
    CNENFRNL
    3 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi7JT85W0lGqqKgAkmZAbKkUqxOtFJRaUlqUVwzkV1VWgEV1lEzBMi6paanJJZllqSBdlVVA0tAcRBgoxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start date" = _t, foo = _t, #"5/9/2022" = _t, #"12/9/2022" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"5/9/2022", Int64.Type}, {"12/9/2022", Int64.Type}}),
    #"Added Diff" = Table.AddColumn(#"Changed Type", "Diff", each let l = List.Reverse(Record.ToList(_)) in l{0}-l{1})
in
    #"Added Diff"

CNENFRNL's avatar
CNENFRNL
Community Champion
3 years ago
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi7JT85W0lGqqKgAkmZAbKkUqxOtFJRaUlqUVwzkV1VWgEV1lEzBMi6paanJJZllqSBdlVVA0tAcRBgoxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start date" = _t, foo = _t, #"5/9/2022" = _t, #"12/9/2022" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"5/9/2022", Int64.Type}, {"12/9/2022", Int64.Type}}),
    #"Added Diff" = Table.AddColumn(#"Changed Type", "Diff", each let l = List.Reverse(Record.ToList(_)) in l{0}-l{1})
in
    #"Added Diff"