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



## bobby_smith (Jul 28, 2019)

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 (Jul 28, 2019)

maybe Table.DemoteHeaders(ROC) and then Change=Column3-Column2


----------



## sandy666 (Jul 28, 2019)

there should be Source instead of ROC, sorry


----------



## bobby_smith (Jul 28, 2019)

I keep getting a error.
Also, if the column is demoted wouldn't this result in text being calculated and numbers and create errors?


----------



## sandy666 (Jul 28, 2019)

post a proper representative example of source data and expected result in table form or link to the shared excel file


----------



## bobby_smith (Jul 28, 2019)

Columns A to C is the original Data.
My desired results are in E to H


ABCDEFGHI1NameMonthValueNameJanuaryFebruaryChange2AJanuary908A908366-5423AFebruary366B765320-4454AMarch620C907223-6845AApril227D766312-4546AMay803E707296-4117AJune9528AJuly4089AAugust24210ASeptember96511AOctober45512ANovember56613ADecember26214BJanuary76515BFebruary32016BMarch83417BApril86318BMay69619BJune73320BJuly48421BAugust57722BSeptember83923BOctober78124BNovember24925BDecember62826CJanuary90727CFebruary22328CMarch30729CApril92130CMay41031CJune28532CJuly95533CAugust85634CSeptember83035COctober78636CNovember54137CDecember51838DJanuary76639DFebruary31240DMarch47241DApril48242DMay38943DJune64344DJuly36345DAugust54146DSeptember27347DOctober98448DNovember20549DDecember82950EJanuary70751EFebruary29652EMarch68553EApril78754EMay59255EJune71656EJuly83557EAugust35158ESeptember47659EOctober56060ENovember80161EDecember654

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

</thead><tbody>

</tbody>*Sheet2*​



<tbody>

</tbody>


----------



## alansidman (Jul 28, 2019)

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 (Jul 28, 2019)

*Name**Month**Value**Name**January**February**Change*AJanuary908​A908​366​-542​AFebruary366​B765​320​-445​AMarch620​C907​223​-684​AApril227​D766​312​-454​AMay803​E707​296​-411​AJune952​AJuly408​AAugust242​ASeptember965​AOctober455​ANovember566​ADecember262​BJanuary765​BFebruary320​BMarch834​BApril863​BMay696​BJune733​BJuly484​BAugust577​BSeptember839​BOctober781​BNovember249​BDecember628​CJanuary907​CFebruary223​CMarch307​CApril921​CMay410​CJune285​CJuly955​CAugust856​CSeptember830​COctober786​CNovember541​CDecember518​DJanuary766​DFebruary312​DMarch472​DApril482​DMay389​DJune643​DJuly363​DAugust541​DSeptember273​DOctober984​DNovember205​DDecember829​EJanuary707​EFebruary296​EMarch685​EApril787​EMay592​EJune716​EJuly835​EAugust351​ESeptember476​EOctober560​ENovember801​EDecember654​


```
[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.


*Name**January**May**Change*A908​803​-105​B765​696​-69​C907​410​-497​D766​389​-377​E707​592​-115​


----------



## sandy666 (Jul 28, 2019)

sandy666 said:


> I mean just simply filter Month column


in PQ editor of course


----------



## bobby_smith (Jul 28, 2019)

@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?


----------



## bobby_smith (Jul 28, 2019)

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 (Jul 28, 2019)

@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 (Jul 28, 2019)

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 (Jul 28, 2019)

@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.



sandy666 said:


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


----------



## sandy666 (Jul 28, 2019)

[ removed ]


----------



## bobby_smith (Jul 28, 2019)

@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.




alansidman said:


> Using PQ
> 
> 
> ```
> ...


----------



## sandy666 (Jul 28, 2019)

sure, I forgot about comma. my fault, sorry

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


----------

