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

bobby_smith

Board Regular
Joined
Apr 16, 2014
Messages
90
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
 
@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
 
Upvote 0

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
I don't know what are you doing there
here is step by step without any error

image.jpg


data is taken from your post so maybe use the same data? :devilish:
 
Last edited:
Upvote 0
@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

image.jpg


data is taken from your post so maybe use the same data? :devilish:
 
Upvote 0
@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

Code:
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"
 
Upvote 0
sure, I forgot about comma. my fault, sorry

btw. don't quote whole posts, use Reply but not Reply With Quote, please
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top