Variation Table

[CPIBecklon]

Good Morning! I would like to take data from different days and different hours and sort them.Ultimately, the date itself will be irrelevant. What I want to do first is to sort each hour from largest value to smallest value. Then, I want to calculate the difference from the lowest value to the value that is right above it, and so on. The function of this table (and ultimately chart) would be to illustrate variation in volume. Previously, I have had to manually do all that is described above in Excel. I'm not even sure if this can be accomplished in Power BI, especially with the sorting. Any ideas would be greatly appreciated.

[CPIBecklon]

@v-stephen-msft I just want to say thank you, again! I went through your procedure and saw that it works beautifully! Also, you've taught me how to make the procedure more streamlined. In some regards, we did the same thing, but your process was far more superior. Thank you!

[v-stephen-msft · Accepted solution]

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.           

[CPIBecklon]

@v-stephen-msft Thank you! I actually did come up with a method...but your method seems shorter. I will try yours.  I may also post my method. Just to give an overview, I did the following: I worked with 3 different queries: (1) A date query coming directly from my data to capture all dates, (2) a key hour query that I created. I did this as I realized some hours may have no data. If I did not account for this, the method I would later use would be impacted, and (3) The query which contained Date, Hour, and Volumes (called Variation Query). I appended the hour query to the date query and manipulated further. Then, when conditions were right, I merged the resulting query into my Variation Query. I then took an approach which would call out seven days of data. The data was placed into seven groups. Group 1 was the lowest volume and group 7 the highest. For example, I created 2 columns: One to identify group 1 and and the other to identify its count. I then filled down in the columns. I took a similar approach with the remaining 6 days. Finally, I created a column that wrote a rule that looked at the group number and compared it to the value that was "filled down". If the group was 1 and the "filled down" value was 1, then the original volume was kept.  However, if the group was two and the "filled down" volume was 1, then I subtracted 2 from 1. As you can imagine, this was a lengthy process. I will give yours a try. Thank you, again!

[CPIBecklon]

One more quick note for someone who might be considering this query. The query is an excellent one, but the one thing I noted is that if an hour has no volume, the query does not count zero volume as 0, which of course, can affect the demo of variation for a given hour or hours.  Therefore, I had to do the following: (1) Use root data to determine exact dates, (2) Append an hour query to account for all hours of the day, realizing that all hours may not be present in the original data, (3) I unpivoted so that I had date and hour columns, (4) I created a query for "Hourly Volume by Day", taken from root data, and merged it with the afforementioned query in Step 2. (4) I employed many of Stephen Tao's steps to finish out the query.  The final result was that any hours that had no volume received a value of zero and were sorted appropriately for calculations.