Forum Discussion
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...
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"
edhans Thanks for your reply! It was very helpful.
I was able to subtract the first column from the second column by changing this line from the code provided by CNENFRNL
#"Added Diff" = Table.AddColumn(#"Changed Type", "Diff", each let l = List.Reverse(Record.ToList(_)) in l{0}-l{1})To
#"Added Diff" = Table.AddColumn(#"Changed Type", "Diff", each let l = List.Reverse(Record.ToList(_)) in l{1}-l{0})
Glad I was able to help Anonymous
Hope your project continues to go well.