Forum Discussion

FieldsG's avatar
FieldsG
New Member
2 years ago
Solved

IDENTIFY THE MOMENT OF VALUE CHANGE

From the series of columns identify the first time I change the value, resulting in the header name of that column.
And in another column identify the last value before the change, also with the name of the header of that column.

But with a power query formula, not in DAX

 

Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    Hi FieldsG,

    Result:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwNzQwNDAwMjAyMjMAAiUdJUsqYUND3ERsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Long OP" = _t, #"2022-01" = _t, #"2022-02" = _t, #"2022-03" = _t, #"2022-04" = _t, #"2022-05" = _t, #"2022-06" = _t, #"2022-07" = _t, #"2022-08" = _t, #"2022-09" = _t, #"2022-10" = _t, #"2022-11" = _t, #"2022-12" = _t, #"2023-01" = _t, #"2023-02" = _t, #"2023-03" = _t, #"2023-04" = _t, #"2023-05" = _t, #"2023-06" = _t, #"2023-07" = _t, #"2023-08" = _t, #"2023-09" = _t, #"2023-10" = _t, #"2023-11" = _t, #"2023-12" = _t]),
    UnpivotedOtherColumns = Table.UnpivotOtherColumns(Source, {"Long OP"}, "Year Month", "Value"),
    AddedIndex = Table.AddIndexColumn(UnpivotedOtherColumns, "Index", 0, 1, Int64.Type),
    Ad_MomentOfChange = Table.AddColumn(AddedIndex, "Moment of Change", each try if [Value] = AddedIndex{[Index]-1}[Value] then null else [Year Month] otherwise null, type text),
    #"Added Custom" = Table.AddColumn(Ad_MomentOfChange, "Last Value Before Change", each if [Moment of Change] = null then null else Ad_MomentOfChange{[Index]-1}[Value], type text),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"Year Month"]), "Year Month", "Value"),
    #"Filled Up" = Table.FillDown(#"Pivoted Column", Table.ColumnNames(#"Pivoted Column")),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([Moment of Change] = "2023-07"))
in
    #"Filtered Rows"

2 Replies

  • dufoq3's avatar
    dufoq3
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi FieldsG,

    Result:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwNzQwNDAwMjAyMjMAAiUdJUsqYUND3ERsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Long OP" = _t, #"2022-01" = _t, #"2022-02" = _t, #"2022-03" = _t, #"2022-04" = _t, #"2022-05" = _t, #"2022-06" = _t, #"2022-07" = _t, #"2022-08" = _t, #"2022-09" = _t, #"2022-10" = _t, #"2022-11" = _t, #"2022-12" = _t, #"2023-01" = _t, #"2023-02" = _t, #"2023-03" = _t, #"2023-04" = _t, #"2023-05" = _t, #"2023-06" = _t, #"2023-07" = _t, #"2023-08" = _t, #"2023-09" = _t, #"2023-10" = _t, #"2023-11" = _t, #"2023-12" = _t]),
    UnpivotedOtherColumns = Table.UnpivotOtherColumns(Source, {"Long OP"}, "Year Month", "Value"),
    AddedIndex = Table.AddIndexColumn(UnpivotedOtherColumns, "Index", 0, 1, Int64.Type),
    Ad_MomentOfChange = Table.AddColumn(AddedIndex, "Moment of Change", each try if [Value] = AddedIndex{[Index]-1}[Value] then null else [Year Month] otherwise null, type text),
    #"Added Custom" = Table.AddColumn(Ad_MomentOfChange, "Last Value Before Change", each if [Moment of Change] = null then null else Ad_MomentOfChange{[Index]-1}[Value], type text),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"Year Month"]), "Year Month", "Value"),
    #"Filled Up" = Table.FillDown(#"Pivoted Column", Table.ColumnNames(#"Pivoted Column")),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([Moment of Change] = "2023-07"))
in
    #"Filtered Rows"