Referencing other rows in Power Query

[Mike Nabil]

I have column with text :


Column 1

A

B

C

D

E

I would like like to insert a custom colum with a formula that would pick up the values from one row above


Custom column :

A

B

C

D

Thanks in advance

Mike

 

[AliGW]

This can be done by creating two queries. In the first, load the data into PQ and add an index column, numbering from 1 onwards. Close and load to connection only.

Now load your data for a second time to PQ. This time, use headers as first row (Transform ribbon), and then add an index column, numbering from 1 onwards. Now go to merge queries (Home ribbon) and select both queries - use the index column for the merge - choose to include all rows from the drop-down.

Once merged, expand the table in the third column and deselect the index column before expanding. You will now have three columns, including the other index column. Remove the second index column and rearrange the other two. Now close and load to your workbook.

M code for first query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1)
in
    #"Added Index"

M code for second query:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
#"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 1, 1),
#"Replaced Value" = Table.ReplaceValue(#"Added Index","Column1","",Replacer.ReplaceText,{"Column1"}),
#"Merged Queries" = Table.NestedJoin(#"Replaced Value",{"Index"},Table1,{"Index"},"Table1",JoinKind.FullOuter),
#"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1", {"Column1"}, {"Table1.Column1"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Table1",{"Index"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Table1.Column1", "Column1"})
in
#"Reordered Columns"

Sample file attached.

 

[AliGW]

In fact, I have just discovered a much easier way!

I have updated the attachment in my first post to include this simple query:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
#"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 0, 1),
[COLOR=#ff0000]#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each List.Skip(#"Added Index"[Column1]){[Index]}?),[/COLOR]
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),
#"Replaced Value" = Table.ReplaceValue(#"Removed Columns","Column1","",Replacer.ReplaceText,{"Column1"}),
#"Reordered Columns" = Table.ReorderColumns(#"Replaced Value",{"Custom", "Column1"})
in
#"Reordered Columns"

I cannot take credit for the section in red, however. A quick Google took me to it here: https://social.technet.microsoft.co...726/add-custom-column-offset?forum=powerquery

 

[AliGW]

Well, even though you’ve never been back, at least I learned something today, so my time wasn’t wasted.