Power Query - How to reference a column position in a formula

[bobby_smith]

Hi guys

Just as a simplified example

I have the following data

Column A Col B Col C
Product May June
Item1 5 2
Item 2 8 3



I would like to create new column names "Change" calculated as "Change = June - May", however I do not want to use the column names as these can change and will cause a error when refreshed.
Is there a way to calculate using Change= Column 3 - Column 2 ?
That way regardless of the month names in column 2 & 3, it will not cause a problem on refresh.

Thanks

 

[bobby_smith]

@sandy666,

I'm getting an error at the Filter step (see below). What is [Month} referring to?

Expression.Error: The field 'Month' of the record wasn't found.
Details:
Name=A
January=908
February=366
March=620
April=227
May=803
June=952
July=408
August=242
September=965
October=455
November=566
December=262

 

[sandy666]

I don't know what are you doing there
here is step by step without any error

data is taken from your post so maybe use the same data?

 

[bobby_smith]

@sandy666,
The error was on my end. It pasted your coded into prior version of the workbook I had.
Its working now, sorry for the confusion.

However, I think a tweak was needed on your M code on the last row before in. I had to remove the comma for it it work.

Thanks for your help on this. Really appreciate it.

I don't know what are you doing there
here is step by step without any error

data is taken from your post so maybe use the same data?

 

[sandy666]

[ removed ]

 

[bobby_smith]

@alansidman,

Sorry for the confusion, but its working.
The error was on my end. It pasted your coded into prior version of the workbook I had.

Thanks for your help with this.

Using PQ

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[Month]), "Month", "Value", List.Sum),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"March", "April", "May", "June", "July", "August", "September", "October", "November", "December"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Differene", each [January]-[February])
in
    #"Added Custom"

 

[sandy666]

sure, I forgot about comma. my fault, sorry

btw. don't quote whole posts, use Reply but not Reply With Quote, please