Summary Table of each refresh

[Lebunim]

Hi guys, 

I'm trying to figure out something and I need your help 🙂

I'm trying to capture summary of data per region and computer type (Desktop/Laptop) and the date of data refresh....

For REGION A Laptops and other columns I can have calculated columns to calculate the count.

I'm just not too sure how to SUMMARIZE it and have Refresh date assigned.

Any ideas?

[amitchandak]

@Lebunim ,The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

[Lebunim]

So I want to have a summary after each refresh. In a separate table. Total number of laptops per region, total number of desktops per region. 
I have Laptops table and Desktops table with no "as of date", so no date stamp for the actual state. I want to be able to trace the change over time and compare month to month. In order to do that I need to have a monthly summary for each Computer type and region. 

Each refresh overrides existing data in those tables. 

[v-rzhou-msft · Accepted solution]

Hi @Lebunim ,

I suggest you to do some transform in Power Query. Firstly, summarize your original table in Power Query Editor to get quantities in each column.

Then Select  [Date of Refresh] > Unpivot Other Columns > Split Column by Positions (0,8) and do some transform like rename, replace. Result is as below.

M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkpNK0otzlBwSSxJVTBU0lEyNjAAkkZg0tQURFoA2bE6aEqNQIrMTYGkoaUFSClYg7kRFqXGYKWGYKUgDSZGINLMAotSE5BSU7Cp5uYgx4BJM5CpsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Date of Refresh" = _t, #"REGION A Laptops" = _t, #"REGION A Desktops" = _t, #"REGION B Laptops" = _t, #"REGION B Desktops" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date of Refresh", type text}, {"REGION A Laptops", Int64.Type}, {"REGION A Desktops", Int64.Type}, {"REGION B Laptops", Int64.Type}, {"REGION B Desktops", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date of Refresh"}, "Attribute", "Value"),
    #"Split Column by Positions" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByPositions({0, 8}), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Positions",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1"," ","",Replacer.ReplaceText,{"Attribute.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"Attribute.1", "Region"}, {"Attribute.2", "Category"}, {"Value", "Quantity"}})
in
    #"Renamed Columns"

Then you can build visuals as you want by the new table.

Best Regards,
Rico Zhou

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