cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
joefred77 Frequent Visitor
Frequent Visitor

Power Query previous row - new column

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?
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Power Query previous row - new column

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])

Regards

MFelix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

7 REPLIES 7
Super User
Super User

Re: Power Query previous row - new column

Hi @joefred77 ,

 

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


Regards

MFelix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




joefred77 Frequent Visitor
Frequent Visitor

Re: Power Query previous row - new column

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"

Super User
Super User

Re: Power Query previous row - new column

Hi @joefred77 ,

 

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


Regards

MFelix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




joefred77 Frequent Visitor
Frequent Visitor

Re: Power Query previous row - new column

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"

Highlighted
Super User
Super User

Re: Power Query previous row - new column

Hi @joefred77 ,

 

Can you share a print screen of your table please?

 

Regards,

MFelix


Regards

MFelix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




joefred77 Frequent Visitor
Frequent Visitor

Re: Power Query previous row - new column

Capture.JPG

Super User
Super User

Re: Power Query previous row - new column

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])

Regards

MFelix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)