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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
I keep getting a error.
Also, if the column is demoted wouldn't this result in text being calculated and numbers and create errors?
 
Upvote 0
post a proper representative example of source data and expected result in table form or link to the shared excel file
 
Last edited:
Upvote 0
Columns A to C is the original Data.
My desired results are in E to H

ABCDEFGHI
1NameMonthValueNameJanuaryFebruaryChange
2AJanuary908A908366-542
3AFebruary366B765320-445
4AMarch620C907223-684
5AApril227D766312-454
6AMay803E707296-411
7AJune952
8AJuly408
9AAugust242
10ASeptember965
11AOctober455
12ANovember566
13ADecember262
14BJanuary765
15BFebruary320
16BMarch834
17BApril863
18BMay696
19BJune733
20BJuly484
21BAugust577
22BSeptember839
23BOctober781
24BNovember249
25BDecember628
26CJanuary907
27CFebruary223
28CMarch307
29CApril921
30CMay410
31CJune285
32CJuly955
33CAugust856
34CSeptember830
35COctober786
36CNovember541
37CDecember518
38DJanuary766
39DFebruary312
40DMarch472
41DApril482
42DMay389
43DJune643
44DJuly363
45DAugust541
46DSeptember273
47DOctober984
48DNovember205
49DDecember829
50EJanuary707
51EFebruary296
52EMarch685
53EApril787
54EMay592
55EJune716
56EJuly835
57EAugust351
58ESeptember476
59EOctober560
60ENovember801
61EDecember654

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2


<tbody>
</tbody>
 
Last edited:
Upvote 0
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
NameMonthValueNameJanuaryFebruaryChange
AJanuary
908​
A
908​
366​
-542​
AFebruary
366​
B
765​
320​
-445​
AMarch
620​
C
907​
223​
-684​
AApril
227​
D
766​
312​
-454​
AMay
803​
E
707​
296​
-411​
AJune
952​
AJuly
408​
AAugust
242​
ASeptember
965​
AOctober
455​
ANovember
566​
ADecember
262​
BJanuary
765​
BFebruary
320​
BMarch
834​
BApril
863​
BMay
696​
BJune
733​
BJuly
484​
BAugust
577​
BSeptember
839​
BOctober
781​
BNovember
249​
BDecember
628​
CJanuary
907​
CFebruary
223​
CMarch
307​
CApril
921​
CMay
410​
CJune
285​
CJuly
955​
CAugust
856​
CSeptember
830​
COctober
786​
CNovember
541​
CDecember
518​
DJanuary
766​
DFebruary
312​
DMarch
472​
DApril
482​
DMay
389​
DJune
643​
DJuly
363​
DAugust
541​
DSeptember
273​
DOctober
984​
DNovember
205​
DDecember
829​
EJanuary
707​
EFebruary
296​
EMarch
685​
EApril
787​
EMay
592​
EJune
716​
EJuly
835​
EAugust
351​
ESeptember
476​
EOctober
560​
ENovember
801​
EDecember
654​

Code:
[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    [COLOR="#FF0000"][B]Filter = Table.SelectRows(Source, each ([Month] = "February" or [Month] = "January")),[/B][/COLOR]
    Pivot = Table.Pivot(Filter, List.Distinct(Filter[Month]), "Month", "Value", List.Sum),
    Demote = Table.DemoteHeaders(Pivot),
    Try = Table.AddColumn(Demote, "Custom", each try [Column3]-[Column2] otherwise "Change"),
    Promote = Table.PromoteHeaders(Try, [PromoteAllScalars=true]),
in
    Promote[/SIZE]

you can change months with filter (red line), I mean just simply filter Month column, eg.

NameJanuaryMayChange
A
908​
803​
-105​
B
765​
696​
-69​
C
907​
410​
-497​
D
766​
389​
-377​
E
707​
592​
-115​
 
Last edited:
Upvote 0
@alansidman,

I copied and pasted your code as is.
I'm getting this error at the Pivoted column step "Expression.Error: The column 'Month' of the table wasn't found.Details:
Month

What is the Source[Month] referring to?
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

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