Power Query previous row - new column

[Anonymous]

I have the following calculated column that retrieves the previous row's "Inv After" column, and it works.

INV BEFORE = CALCULATE(MAX(Table1[Inv After]),FILTER(Table1,Table1[Index]=EARLIER(Table1[Index])-1))

However I want to perform this in the query editor rather than a calcualted table in my visual. Any idea how to translate this to make this a new column in the query editor?

[MFelix]

Hi @Anonymous ,

Taking into account that you already have an index column you need to add a custom column with the following code:

 #"Previous Step Name"{[Index] -1}[Inv after]

If you want that the first step is blank then you should use something like this

if [Index] = 1 then null else #"Previous Step Name"{[Index] -1}[Inv after]

Regards,

MFelix

[Anonymous]

I see the logic here, but for some reason I can't get it to work. Here is my query. I want to add the new column at the very end.

let
Source = Sql.Database("db01", "view"),
dbo_Table1 = Source{[Schema="dbo",Item="Table1"]}[Data],
#"Filtered Rows" = Table.SelectRows(dbo_Table1, each [Warehouse] = "A" or [Warehouse] = "B" or [Warehouse] = "C"),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "-Line", each [Order Line]*-1),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Item", "Transaction Datetime Converted", "Warehouse", "Order Type","Order Number", "-Line"}, {{"Inv After", each List.Last([Inventory After Transaction]), type number}}),
#"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Item", Order.Ascending},{"Warehouse",Order.Ascending},{"Transaction Datetime Converted",Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1),
#"Inserted Merged Column" = Table.AddColumn(#"Added Index", "SO Line", each Text.Combine({Text.From([Order Number], "en-US"), Text.From([#"-Line"], "en-US")}, ""), type text)
in
#"Inserted Merged Column"

[MFelix]

Hi @Anonymous ,

You need to do something similar to this:

 #"Inserted Merged Colunn"{[Index] -1}[Inv After]

The full step to copy to the advance query editor should be similar to this

= Table.AddColumn(#"Added Index", "Custom", each #"Inserted Merged Colunn"{[Index] -1}[Inv After])

Regards,

MFelix

[Anonymous]

Hmmm....I'm almost there, I get an Error in all of the custom1 fields. Here are my last few lines of code

for some reason it is not returning the previous index's INV AFTER value

#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1),
#"Inserted Merged Column" = Table.AddColumn(#"Added Index", "SO Line", each Text.Combine({Text.From([Order Number], "en-US"), Text.From([#"-Line"], "en-US")}, ""), type text),
#"Added Custom1" = Table.AddColumn(#"Inserted Merged Column", "Custom", each {[Index]-1}[Inv After])
in
#"Added Custom1"

[MFelix]

Hi @Anonymous ,

Can you share a print screen of your table please?

Regards,

MFelix

[Anonymous]

[MFelix · Accepted solution]

Your last step is incorrect is must be written like this

#"Added Custom1" = Table.AddColumn(#"Inserted Merged Column", "Custom", each #"Inserted Merged Column"{[Index]-1}[Inv After])