Forum Discussion
Getting values from new column previous row in Excel power query
Hi,
Please see the data done in Excel sheet, which works fine
First 5 columns are given values and last 4 columns are generated through below formulas
- PBA - =IF([@Date]=MIN([Date]), 0, INDEX([NBA], ROW()-2, 1))
- PBAMT - =IF([@Date]=MIN([Date]), 0, INDEX([NBAMT], ROW()-2, 1))
- NBA - =IF([@Side]="B", IF([@Date]=MIN([Date]), [@Amt]/[@Qty], (([@PBAMT]+[@Amt])/[@RQ])), [@PBA])
- NBAMT - =[@RQ]*[@NBA]
As you can see, PBA & PBAMT are NBA & NBAMT from previous row respectively. I want to have the same functionality in Power Query. I tried to do Merge method with the same query as found in some YouTube tutorial. But it doesn't in this context. Not able to figure out a solution yet. Can anyone help please.
Thank you,
Hi gafoorgk
Thank you for engaging with the Microsoft Fabric Community.
In addition to Akash_Varuna for his response, as i have replicated the scenario and it works fine for me.
Could you please have look into the attached .pbix file.
Note: Power Query M code for reference
***********************************************************= let
Source = Table.FromRecords({
[Date=#datetime(2025, 2, 21, 14, 48, 0), Side="B", Qty=10, Amt=1574.60, RQ=10],
[Date=#datetime(2025, 2, 21, 15, 21, 0), Side="B", Qty=100, Amt=15825.00, RQ=110],
[Date=#datetime(2025, 2, 21, 15, 24, 0), Side="S", Qty=90, Amt=14131.44, RQ=20],
[Date=#datetime(2025, 2, 21, 15, 29, 0), Side="B", Qty=30, Amt=4741.50, RQ=50],
[Date=#datetime(2025, 2, 21, 15, 30, 0), Side="S", Qty=10, Amt=1582.10, RQ=40]
}),SortedTable = Table.Sort(Source, {{"Date", Order.Ascending}}),
DataList = Table.ToRecords(SortedTable),
ComputedList = List.Accumulate(
DataList,
[PBA=0, PBAMT=0, Result={}],
(state, currentRow) =>
let
PrevPBA = state[PBA],
PrevPBAMT = state[PBAMT],NBA = if currentRow[Side] = "B"
then if currentRow[Date] = DataList{0}[Date]
then currentRow[Amt] / currentRow[Qty]
else (PrevPBAMT + currentRow[Amt]) / currentRow[RQ]
else PrevPBA,
NBAMT = currentRow[RQ] * NBA,UpdatedRow = [
Date = currentRow[Date],
Side = currentRow[Side],
Qty = currentRow[Qty],
Amt = currentRow[Amt],
RQ = currentRow[RQ],
PBA = PrevPBA,
PBAMT = PrevPBAMT,
NBA = NBA,
NBAMT = NBAMT
],UpdatedResult = state[Result] & {UpdatedRow}
in
[PBA=NBA, PBAMT=NBAMT, Result=UpdatedResult]
),FinalTable = Table.FromRecords(ComputedList[Result]),
ChangeTypes = Table.TransformColumnTypes(FinalTable, {
{"Date", type datetime},
{"Side", type text},
{"Qty", Int64.Type},
{"Amt", type number},
{"RQ", Int64.Type},
{"PBA", type number},
{"PBAMT", type number},
{"NBA", type number},
{"NBAMT", type number}
})
in
ChangeTypes***********************************************************
If the above information helps you, please give us a Kudos and marked the reply Accept as a Solution.
Thanks,
Cheri Srikanth
6 Replies
Hi gafoorgk
Thank you for engaging with the Microsoft Fabric Community.
In addition to Akash_Varuna for his response, as i have replicated the scenario and it works fine for me.
Could you please have look into the attached .pbix file.
Note: Power Query M code for reference
***********************************************************= let
Source = Table.FromRecords({
[Date=#datetime(2025, 2, 21, 14, 48, 0), Side="B", Qty=10, Amt=1574.60, RQ=10],
[Date=#datetime(2025, 2, 21, 15, 21, 0), Side="B", Qty=100, Amt=15825.00, RQ=110],
[Date=#datetime(2025, 2, 21, 15, 24, 0), Side="S", Qty=90, Amt=14131.44, RQ=20],
[Date=#datetime(2025, 2, 21, 15, 29, 0), Side="B", Qty=30, Amt=4741.50, RQ=50],
[Date=#datetime(2025, 2, 21, 15, 30, 0), Side="S", Qty=10, Amt=1582.10, RQ=40]
}),SortedTable = Table.Sort(Source, {{"Date", Order.Ascending}}),
DataList = Table.ToRecords(SortedTable),
ComputedList = List.Accumulate(
DataList,
[PBA=0, PBAMT=0, Result={}],
(state, currentRow) =>
let
PrevPBA = state[PBA],
PrevPBAMT = state[PBAMT],NBA = if currentRow[Side] = "B"
then if currentRow[Date] = DataList{0}[Date]
then currentRow[Amt] / currentRow[Qty]
else (PrevPBAMT + currentRow[Amt]) / currentRow[RQ]
else PrevPBA,
NBAMT = currentRow[RQ] * NBA,UpdatedRow = [
Date = currentRow[Date],
Side = currentRow[Side],
Qty = currentRow[Qty],
Amt = currentRow[Amt],
RQ = currentRow[RQ],
PBA = PrevPBA,
PBAMT = PrevPBAMT,
NBA = NBA,
NBAMT = NBAMT
],UpdatedResult = state[Result] & {UpdatedRow}
in
[PBA=NBA, PBAMT=NBAMT, Result=UpdatedResult]
),FinalTable = Table.FromRecords(ComputedList[Result]),
ChangeTypes = Table.TransformColumnTypes(FinalTable, {
{"Date", type datetime},
{"Side", type text},
{"Qty", Int64.Type},
{"Amt", type number},
{"RQ", Int64.Type},
{"PBA", type number},
{"PBAMT", type number},
{"NBA", type number},
{"NBAMT", type number}
})
in
ChangeTypes***********************************************************
If the above information helps you, please give us a Kudos and marked the reply Accept as a Solution.
Thanks,
Cheri SrikanthHello Srikanth,
I just had a quick glance on your solution and tried. It works!. I must say I am very impressed. Thank you.
I need to dissect this solution and apply it in my bigger picture. Once it fits well, I will sure let you know with lots of Kudos.
Thanks you again,
Your solution worked well for me Srikanth. PQ's column-by-colum flow is bypassed to be rows-by-rows flow by this List.Accummulate function. I didn't know this and couldn't find it anywhere as a solution for my problem when I searched.
Thanks again.
Hi gafoorgk Could yoy try with Indexing and creating a custom column
Add an Index Column:
- Go to the "Add Column" tab and select Index Column > From 0 or 1. This will help identify rows for referencing the previous row.
Create Custom Columns for Calculations:
For PBA:
PBA = if [Date] = List.Min(#"Previous Step"[Date]) then 0 else Table.AddColumn(#"Previous Step", "PBA", each #"Previous Step"[NBA]{[Index]-1})- Replace #"Previous Step" with your previous table step name.
- Use the Index to refer to the previous row's value.
For PBAMT:
PBAMT = if [Date] = List.Min(#"Previous Step"[Date]) then 0 else #"Previous Step"[NBAMT]{[Index]-1}For NBA:
NBA = if [Side] = "B" and [Date] <> List.Min(#"Previous Step"[Date]) then [Amt] / [Qty] else [PBA]For NBAMT:
NBAMT = [RQ] * [NBA]
If this post helped please do give a kudos and accept this as a solution
Thanks In Advance
Thanks Akash.
I already tried the method you explained as I found this method while I tried googling. In addition to it, I also tried: Add 2 index cols, one starting from 0 and other starting from 1. Then merging with self query, which does more or less the same thing as you mentioned.
In both cases problem is: PBA gets value of NBA in previous row and NBAMT references PBAMT from previous row. A kind of circular calling, but protected from crashing by conditional clauses. To make myself more clear, I am attaching the sample file here with custom columns created as you adviced. Please do see.
https://docs.google.com/spreadsheets/d/1cKo00vTkm7YG4-DYlwUBntywaxAVu2a7?rtpof=true&usp=drive_fs
Thanks,
Hi gafoorgk
"Thank you for your kind words! I'm glad the solution worked well for you. If you have any further questions or need additional assistance, feel free to reach out. Happy to help!"
Thanks and Regards,
Cheri Srikanth.
