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


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



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



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



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


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

Proud to be a Datanaut!




View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 321 members 3,326 guests
Please welcome our newest community members: