Need Help Migrating Tableau Report and Alteryx Workflow to Power BI

Hi nidish Use Table.AddIndexColumn to add indices and Table.Buffer to reference previous rows. For formulas like [Row-1:Index]+1, create an index column and add 1 to shift values. For conditional logic, use if...then...else with buffered tables to compare and derive values from prior rows. Break complex workflows into smaller steps for clarity and performance.

Hi nidish ,

Thanks for using Microsoft Fabric Community and sharing your scenario.

Yes, it’s possible to replicate Alteryx multi-row formulas using Power Query without relying on DAX or external tools. To handle logic like [Row-1:Index] + 1 or conditional row comparisons, you can use an index column and a shifted self-join approach.

I tested the logic using this sample data:

Power Query steps used:

let
    Source = Table.FromRows(
        {
            {"1", "A", "X", "01-01-2024"},
            {"2", "A", "X", "02-01-2024"},
            {"3", "A", "Y", "03-01-2024"},
            {"4", "B", "X", "04-01-2024"},
            {"5", "B", "X", "05-01-2024"}
        },
        {"Index", "ID", "Group A", "Extract Date"}
    ),

    ChangedType = Table.TransformColumnTypes(Source, {
        {"Index", Int64.Type}, {"ID", type text},
        {"Group A", type text}, {"Extract Date", type date}
    }),

    AddedRowNumber = Table.AddIndexColumn(ChangedType, "RowNumber", 0, 1, Int64.Type),
    PrevRows = Table.AddIndexColumn(ChangedType, "RowNumber", 1, 1, Int64.Type),
    RenamedPrev = Table.RenameColumns(PrevRows, {
        {"Index", "Prev_Index"},
        {"ID", "Prev_ID"},
        {"Group A", "Prev_Group A"},
        {"Extract Date", "Prev_Extract Date"}
    }),

    Merged = Table.NestedJoin(AddedRowNumber, {"RowNumber"}, RenamedPrev, {"RowNumber"}, "PrevRow", JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(Merged, "PrevRow", {
        "Prev_Index", "Prev_ID", "Prev_Group A", "Prev_Extract Date"
    }),

    AddedIndexPlus1 = Table.AddColumn(Expanded, "Row-1_IndexPlus1", each if [Prev_Index] <> null then [Prev_Index] + 1 else null, Int64.Type),
    AddedConditional = Table.AddColumn(AddedIndexPlus1, "Conditional Extract Date", each
        if [Prev_ID] = [ID] and [Prev_Group A] = [Group A] then [Prev_Extract Date] else null, type nullable date),
    #"Sorted Rows" = Table.Sort(AddedConditional,{{"Index", Order.Ascending}})
in
    #"Sorted Rows"

Some best practices to keep in mind while implementing multi-row logic in Power Query:

1. Filter and remove unnecessary columns early to optimize performance

2. Use Table.Buffer for small lookup tables before merges, if reused

3. Clean and standardize key columns before joining

4. Perform merges and sorts later in the query chain when possible

Here are some resources which you might find useful:
Power Query - Methods to Refer to Previous Row - T... - Microsoft Fabric Community

Best practices when working with Power Query - Power Query | Microsoft Learn

Hope this helps. Please reach out for further assistance.
If this post helps, then please consider to Accept as the solution to help the other members find it more quickly.

Thank you.

Please find the attached .pbix file for reference.