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
Highlighted
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"

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

Highlighted
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

Top Solution Authors
Top Kudoed Authors (Last 30 Days)