Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

7 REPLIES 7
MFelix
Super User
Super User

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

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"

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

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"

Hi @Anonymous ,

 

Can you share a print screen of your table please?

 

Regards,

MFelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Capture.JPG

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

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.