Variation Table

Hi CPIBecklon ,

Then the original data you have not modified is imported into Power BI, and it should be the following table in Power Query Editor.

Then you can follow my steps below to get the desired result.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY1RCgAhCETv0ndBamZ7ltj7X2NnJGJBB306unfRrtqsSalFJmQgHYmwhE4qRj4eUlaC8q3XbUDaKcft6cnFlcojk2LsVdbfHkDr/Ba51hTlyeCGsw+OA8/fDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, #"0" = _t, #"1" = _t, #"2" = _t, #"3" = _t, #"4" = _t, #"5" = _t, #"6" = _t, #"7" = _t, #"8" = _t, #"9" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}, {"0", Int64.Type}, {"1", Int64.Type}, {"2", Int64.Type}, {"3", Int64.Type}, {"4", Int64.Type}, {"5", Int64.Type}, {"6", Int64.Type}, {"7", Int64.Type}, {"8", Int64.Type}, {"9", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Column1"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Column1"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Attribute"}, 
    {{"Count", 
    each Table.AddIndexColumn(
        Table.Sort(_,{{"Value", 
        Order.Descending}}), "Row Rank",0,1), 
    type table [Attribute=nullable text, 
        Value=nullable number,
        Row Rank=nullable number]}
    }
),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Value", "Row Rank"}, {"Value", "Row Rank"}),
    #"Grouped Rows1" = Table.Group(#"Expanded Count", {"Row Rank"}, {{"Count", each _, type table [Attribute=text, Value=nullable number, Row Rank=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows1", "Custom", each List.Range(
   #"Grouped Rows1"[Row Rank],
  [Row Rank]-1,
   1)),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Extracted Values", {{"Custom", null}}),
    #"Expanded Count1" = Table.ExpandTableColumn(#"Replaced Errors", "Count", {"Attribute", "Value"}, {"Attribute", "Value"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Count1",{{"Custom", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"Row Rank", "Attribute"}, #"Changed Type1", {"Custom", "Attribute"}, "Changed Type1", JoinKind.LeftOuter),
    #"Expanded Changed Type1" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type1", {"Value"}, {"Value.1"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Changed Type1", "Difference", each if [Value]-[Value.1]=null then [Value] else [Value]-[Value.1]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Value", "Custom", "Value.1"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Attribute]), "Attribute", "Difference", List.Sum),
    #"Removed Columns2" = Table.RemoveColumns(#"Pivoted Column",{"Row Rank"})
in
    #"Removed Columns2"

Expected result:

You can download my attachment for more details.

Reference:

Adding a row rank based on a different column with Power Query – data witches (data-witches.com)

Value from previous row – Power Query, M language – Trainings, consultancy, tutorials (exceltown.com)

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.