Add difference column based on dynamic column names

[Anonymous]

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 account these dynamic column names. Is this possible? Any help is much appreciated!

Start date	05/09/2022	12/09/2022
Stock	6	9
Returns	9	5
Defective	17	10

Expected results:

Start date	05/09/2022	12/09/2022	Difference
Stock	6	9	-3
Returns	9	5	4
Defective	17	10	7

[CNENFRNL · Accepted solution]

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]

The proper way to do this in Power BI is to transform your data as follows:

Then you do the math easily in DAX.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi7JT85W0lEyA2JLpVidaKWg1JLSorxiMF9HyRQs5pKalppcklmWChQxNAcRBkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start date" = _t, #"5/9/2022" = _t, #"12/9/2022" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Start date"}, "Attribute", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", Int64.Type}, {"Attribute", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Attribute", "Date"}}),
    #"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[#"Start date"]), "Start date", "Value", List.Sum)
in
    #"Pivoted Column"

 However, if you need the result in Power Query for other reasons, this will always subtract the 2nd column from the 1st column:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi7JT85W0lEyA2JLpVidaKWg1JLSorxiMF9HyRQs5pKalppcklmWChQxNAcRBkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start date" = _t, #"5/9/2022" = _t, #"12/9/2022" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"5/9/2022", Int64.Type}, {"12/9/2022", Int64.Type}}),
    Difference = 
        Table.AddColumn(
            #"Changed Type",
            "Difference",
            each
                let
                    varFirstColumn = Table.ColumnNames(#"Changed Type"){1},
                    varSecondColumn = Table.ColumnNames(#"Changed Type"){2}
                in
                Record.Field(_, varFirstColumn) - Record.Field(_, varSecondColumn),
            Int64.Type
        )
in
    Difference

Note however it will not dynamically change the column types based on the changing names. I'd recommend changing the names using the logic in this blog I wrote Renaming A Column In Power Query Based On Position — ehansalytics or modifying it to change the data types. But having columns that are actual dates is generally a bad practice when it comes into the Power BI data model.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[Anonymous]

@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})

[edhans]

Glad I was able to help @Anonymous
Hope your project continues to go well.