Subtract Columns based on Position during Transform Data Stage

[zia_ward]

My goal is to add a new column during the data transform stage, which is the difference (subtract) between the last column and the 30th-column-before-the-last-column. 

I can't go by column names because covid data is refreshed every day and most of the column names are Dates . 

Any help would be appreciated. New to Power Query.

Zia

[v-yingjl · Accepted solution]

Hi @zia_ward ,

You can try this query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VdBBCoAwDETRu7gWmiZNW88i3v8aimDnSzef8shiznNz81qet+0rU+32tYs4iIOESIBEiUWaSANpICmSIAnSRTpIBxkiA2SATJEJMkus/6MwU60r1bCdcTzDofqb9R3wum4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"2021/1/1" = _t, #"2021/1/15" = _t, #"2021/1/20" = _t, #"2021/2/1" = _t, #"2021/2/15" = _t, #"2021/2/20" = _t, #"2021/3/1" = _t, #"2021/3/15" = _t, #"2021/3/30" = _t, #"2021/4/1" = _t, #"2021/4/15" = _t, #"2021/4/30" = _t, #"2021/5/1" = _t, #"2021/5/15" = _t, #"2021/5/30" = _t, #"2021/6/1" = _t, #"2021/6/15" = _t, #"2021/6/30" = _t, #"2021/7/1" = _t, #"2021/7/15" = _t, #"2021/7/30" = _t, #"2021/8/1" = _t, #"2021/8/15" = _t, #"2021/8/31" = _t, #"2021/9/1" = _t, #"2021/9/15" = _t, #"2021/9/30" = _t, #"2021/10/1" = _t, #"2021/10/15" = _t, #"2021/10/31" = _t, #"2021/11/15" = _t, #"2021/12/15" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"2021/1/1", type date}, {"2021/1/15", type date}, {"2021/1/20", type date}, {"2021/2/1", type date}, {"2021/2/15", type date}, {"2021/2/20", type date}, {"2021/3/1", type date}, {"2021/3/15", type date}, {"2021/3/30", type date}, {"2021/4/1", type date}, {"2021/4/15", type date}, {"2021/4/30", type date}, {"2021/5/1", type date}, {"2021/5/15", type date}, {"2021/5/30", type date}, {"2021/6/1", type date}, {"2021/6/15", type date}, {"2021/6/30", type date}, {"2021/7/1", type date}, {"2021/7/15", type date}, {"2021/7/30", type date}, {"2021/8/1", type date}, {"2021/8/15", type date}, {"2021/8/31", type date}, {"2021/9/1", type date}, {"2021/9/15", type date}, {"2021/9/30", type date}, {"2021/10/1", type date}, {"2021/10/15", type date}, {"2021/10/31", type date}, {"2021/11/15", type date}, {"2021/12/15", type date}}),
    ColumnNames = Table.ColumnNames(#"Changed Type"),
    #"Added column" = 
        Table.AddColumn(
            #"Changed Type","Diff",each 
            let Last30 = List.LastN(ColumnNames,31) in 
                Number.From(Date.From(List.Last(Last30)) - Date.From(List.First(Last30))),
            type number
        )
in
    #"Added column"

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Ok, that actually simplifies it--you can change the final step to:

= let LastValue = ColumnCount, FirstValue = ColumnCount-30 in ColumnNames{LastValue} - ColumnNames{FirstValue}

--Nate

[zia_ward]

Hi Nate,

I get the following error when I add the last step:

Expression.Error: There weren't enough elements in the enumeration to complete the operation.

Any clue why this is happening? Here is what I did:

1- I right click on ColumnCount step and added "Insert Step After". I then added the exact formula

in the formula bar: = let LastValue = ColumnCount, FirstValue = ColumnCount-30 in ColumnNames{LastValue} - ColumnNames{FirstValue}

2 - I get the error message.

Another error that I get when I adjust the position of last column, (= let LastValue = ColumnCount-1, FirstValue = ColumnCount-30 in ColumnNames{LastValue} - ColumnNames{FirstValue} )

Expression.Error: We cannot apply operator - to types Text and Text.
Details:
Operator=-
Left=9/1/21
Right=8/3/21

Thanks,

Zia

[Anonymous]

You can first count the number of columns that you have, use some math to get positions Column Count and Column Count - 30, and then use Table.ColumnNames to get a list of the column names.  Use the positions of the column names to extract the two column names from the list, like {Column Names}{Variable that Represents List Position}.  So if your current last step is named LastStep, let's make a new step named ColumnNames.  Then in the formula bar, type:

= Table.ColumnNames(Last Step)

Add a new step, call it ColumnCount:

= Table.ColumnCount(LastStep)

Now we can add the column--I'm assuming you are subtracting a date from a date.  Add a step named NewColumn, and in the formula bar, type:

= let LastValue = ColumnCount, FirstValue = ColumnCount-30 in Duration.TotalDays(ColumnNames{LastValue} - ColumnNames{FirstValue})

--Nate

[zia_ward]

Hi Nate,

Thanks a lot for your solution. Everything is working fine. Since the values in the columns are not dates but numbers, I have to replace Duration.TotalDays with something else. What should I replace the following code with: Duration.TotalDays(ColumnNames{LastValue} - ColumnNames{FirstValue})

Thanks again!

-Zia