Forum Discussion

gafoorgk's avatar
gafoorgk
Frequent Visitor
1 year ago
Solved

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...
Need Help
  • v-csrikanth's avatar
    v-csrikanth
    1 year ago

    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

Akash_Varuna's avatar
Akash_Varuna
Super User
1 year ago

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
  • gafoorgk's avatar
    gafoorgk
    Frequent Visitor
    1 year ago

    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,