CODEWITHSUNDEEP
powerquerymdata-transform
Wyatt
Wyatt

Reputation: 11

How to Reference Previous Row Value in Power Query for Custom Column Logic?

I’m working in Power Query and trying to create a Custom Column that mimics the following Excel formula, im struggling with mcode:

=IF(A2 > 0, IF(A2 = A1, 0, A2), A2)

The goal is:

If the current value in the Forecast.P6 Forecast column is greater than 0: Check if it is the same as the value in the previous row. If it is the same as the previous row’s value, return 0. If not, return the current value. If the current value is 0 or less, just return the current value. Here’s an example of what I want:

Forecast.P6 Forecast Forecast P6
100 100
100 0
200 200
200 0
300 300

What I’ve Tried: I’ve added an Index column to try and access previous rows with Table.PositionOf, but it still didn’t behave as expected. I tried adding shifted lists (e.g., List.RemoveFirstN), but referencing values in the same query has been tricky.

Issue: The above M code either returns errors or tables instead of values. It feels like I'm hitting a design limitation with how Power Query handles row-by-row comparisons. How can I properly implement this logic so that I can compare a value with the previous row’s value?

Any Help or Suggestions? Is there a better way to reference the previous row in Power Query? Should I try a different approach like using lists or another technique?

Upvotes: 1

Views: 495

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60389

In Power Query, you can use an Index column to refer to a particular row. So code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Forecast.P6 Forecast", Int64.Type}}),

//Add Index column to easily reference previous row
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),

//Add the result column
    #"Added Custom" = Table.AddColumn(#"Added Index", "Forecast P6", each

        //Special case for first row which would ==> error
        try
            if [Forecast.P6 Forecast] = #"Changed Type"[Forecast.P6 Forecast]{[Index]-1} 
            then 0 
            else [Forecast.P6 Forecast]
        otherwise [Forecast.P6 Forecast], Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
    #"Removed Columns"

You could also shift the column directly, and then omit using the index column completely:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Forecast.P6 Forecast", Int64.Type}}),


    #"Add Shifted Column" = Table.FromColumns(
        Table.ToColumns(#"Changed Type")
        & {{null} & List.RemoveLastN(#"Changed Type"[Forecast.P6 Forecast])},
        type table[Forecast.P6 Forecast=Int64.Type, Shifted = Int64.Type]),

    #"Add Forecast Column" = Table.AddColumn(#"Add Shifted Column","Forecast P6", 
        each if [Forecast.P6 Forecast] = [Shifted] then 0 else [Forecast.P6 Forecast], Int64.Type),

    #"Removed Columns" = Table.RemoveColumns(#"Add Forecast Column",{"Shifted"})
in
    #"Removed Columns"

Read the code and explore the Applied Steps to better understand what is going on.

enter image description here

Upvotes: 1

Michal
Michal

Reputation: 6099

This is a step by step approach which can be made more compact, it's the simplest approach to your problem:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    // Step 1: Add an Index column to reference previous rows
    AddIndex = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),

    // Step 2: Add a column to shift the "Forecast.P6 Forecast" column down by one row
    PreviousRow = Table.AddColumn(AddIndex, "Previous Forecast", each try AddIndex{[Index]-2}[Forecast.P6 Forecast] otherwise null),
    
    // Step 3: Add a new column to compare the values in "Forecast.P6 Forecast"
   Compare = Table.AddColumn(PreviousRow, "Compare", each if [Previous Forecast]=[Forecast.P6 Forecast] then 0 else [Previous Forecast])
in
   Compare

Upvotes: 1

Related Questions