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 through classes, but need a fix for this now if I can find one.

 

So I have a daily reading on a meter that restarts once it hits 10million. The reading is taken once a day, (though a solution that also works for multiple times a day would be great), and the daily total is calculated outside of Power Query by taking the daily total and subtracting the day priors daily total. When the meter restarts at 10million, this is obviously an issue, as it will take a much smaller number and subtract a much higher number, resulting in a negative total.

 

I tried using a bit of the ChatGBT and it came up with this formula

 

= if [Date] = List.Min(MyTable[Date]) then [Meter1] else let PrevRow = MyTable{List.PositionOf(MyTable[Date],[Date])-1}, Yesterday = Record.Field(PrevRow, "Meter1"), Today = [Meter1] in if Today >= Yesterday then Today else 10000000 - Yesterday + Today

 

In this script:

  • if [Date] = List.Min(MyTable[Date]) then [Meter1] checks if the current row's date is the earliest date in the date column. If it is, it returns the value of the 'Meter1' column because there's no "yesterday" value to compare with.
  • let PrevRow = MyTable{List.PositionOf(MyTable[Date],[Date])-1} assigns the previous row's data to PrevRow.
  • Yesterday = Record.Field(PrevRow, "Meter1") assigns the previous row's 'Meter1' value to Yesterday.
  • Today = [Meter1] assigns the current row's 'Meter1' value to Today.
  • The last line uses an if statement to compare the 'Today' and 'Yesterday' values according to your criteria.

This script works under the assumption that you have one record per day. If you have multiple records per day and you want to compare the last record of the previous day to the first record of the current day, you would need a different approach.

 

Can someone give me a hand with this? We can completely ignore the AI if that's a rabbit hole that isn't helping.

 

Thanks in advance.

 

 

Need Help

3 Replies

Replies have been turned off for this discussion
  • Payeras_BI's avatar
    Payeras_BI
    Solution 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
        Solution 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