Forum Discussion

conwayzerbeam's avatar
conwayzerbeam
New Member
3 years ago

Custom Column Formula Syntax

Hello all,   So I am attempting to create a custom column to accomodate a certain situation and I for the life of me can't figure it out. I'm relatively new to PowerBi/Query so still working throug...
Need Help
Payeras_BI's avatar
Payeras_BI
Icon for Solution Sage rankSolution Sage
3 years ago

Hi conwayzerbeam ,

I hope I understood what you were looking after:

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTQNzDTNzIwMlbSUbI0AAOlWB2ghCGqhCFcwghZAgIgEsZIEiYIg0xwqTdFkjBUio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, MeterReading = _t]),
    #"Changed Type_MeterReading" = Table.TransformColumnTypes(Source,{{"MeterReading", Currency.Type}}),
    AdjustedMeterReading = Table.AddColumn(#"Changed Type_MeterReading", "AdjustedMeterReading", 
    (row) => 
        let
            currentRow = Table.PositionOf(#"Changed Type_MeterReading", row),
            currentMeterReading = row[MeterReading],
            result = List.Accumulate({0..currentRow}, 0, (state, index) => 
                let
                    prevRow = index - 1,
                    prevMeterReading = if prevRow >= 0 then #"Changed Type_MeterReading"{prevRow}[MeterReading] else null,
                    currMeterReading = #"Changed Type_MeterReading"{index}[MeterReading]
                in
                    if prevMeterReading <> null and currMeterReading < prevMeterReading then state + 10000000 else state
            )
        in
            result + currentMeterReading
    ),
    #"Changed Type_AdjustedMeterReading" = Table.TransformColumnTypes(AdjustedMeterReading,{{"AdjustedMeterReading", Currency.Type}})
in
    #"Changed Type_AdjustedMeterReading"

 

Regards,

 

 

conwayzerbeam's avatar
conwayzerbeam
New Member
3 years ago

Judging by your pictures, I think that is mostly what I want.
Where would I input that code though? Here?

 

  • Payeras_BI's avatar
    Payeras_BI
    Icon for Solution Sage rankSolution Sage
    3 years ago

    Hi conwayzerbeam,

    Yes, paste below code there but before substitute in the code all appearances of YourPreviousStep and [MeterReading] with the ones that correspond (see the screenshot for your reference).

    (row) => 
        let
            currentRow = Table.PositionOf(YourPreviousStep, row),
            currentMeterReading = row[MeterReading],
            result = List.Accumulate({0..currentRow}, 0, (state, index) => 
                let
                    prevRow = index - 1,
                    prevMeterReading = if prevRow >= 0 then YourPreviousStep{prevRow}[MeterReading] else null,
                    currMeterReading = YourPreviousStep{index}[MeterReading]
                in
                    if prevMeterReading <> null and currMeterReading < prevMeterReading then state + 10000000 else state
            )
        in
            result + currentMeterReading