Forum Discussion

tockert's avatar
tockert
Regular Visitor
4 years ago
Solved

Count across multiple columns

I have a table with multiple team members shirt style (Men or Women) and shirt size.  

5 different rows for each one of the above, and they are named like this.

TM#1_Shirt_Style, TM#1_Shirt Size... TM#5_Shirt_Style, TM#5_Shirt_Size

      Men                            XL                   Women                   M

 

I need to count all of this data to summerize it down to a table with how many mens different sized shirts and the same for womens shirts.

So I end up with something like this

 

Style     S   M  L  XL  XXL   XXXL

Men      0    2  4   4     5       2

Women  4   3   1   1     0       0

 

I don't care if it totals all of that data or not.

The table is called events.

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi tockert ,

     

    According to your screenshot, I create a sample to have a test. Here I suggest you to transform your table in Power Query Editor, then you can get result by creating a matrix visual.

    M code:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZZBLDoAgDETv0jWXEBHlt9aEsHSp3n8nHyMtXRAyvOl0QowwgYD9uc473z6fokNVh4ckIsz4QYDs8jOoIaEPhMrlwBVL2P6XwhfGNa2gUWBbsZIRgXTjOLIa8MZiMKSBgeEPLMGWFXS0oBvnPeWeBQRqIDKlFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Team Member #2 Name" = _t, #"Team Member #2 Shirt Style" = _t, #"Team Member #2 Size" = _t, #"Team Member #3 Name" = _t, #"Team Member #3 Shirt Style" = _t, #"Team Member #3 Size" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Team Member #2 Name", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Duplicated Column" = Table.DuplicateColumn(#"Unpivoted Other Columns", "Attribute", "Attribute - Copy"),
    #"Extracted First Characters" = Table.TransformColumns(#"Duplicated Column", {{"Attribute", each Text.Start(_, 14), type text}}),
    #"Extracted Text Range" = Table.TransformColumns(#"Extracted First Characters", {{"Attribute - Copy", each Text.Middle(_, 15, 100), type text}}),
    #"Pivoted Column" = Table.Pivot(#"Extracted Text Range", List.Distinct(#"Extracted Text Range"[#"Attribute - Copy"]), "Attribute - Copy", "Value"),
    #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Attribute", Order.Ascending}, {"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns"

    New Table:

    Then create a matrix and get result directly.

    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.

     

     

6 Replies

  • tockert's avatar
    tockert
    Regular Visitor
    4 years ago

    Here is a snip of the table from Excel that I import into Power BI.

    Trying to count all of the different sized shirts by men or women.

    Thanks

  • tockert's avatar
    tockert
    Regular Visitor
    4 years ago

    I am currently doing it by each team like this.

    This still requires some manual counting to know all the different sizes by gender.

     

    Thanks

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi tockert ,

       

      According to your screenshot, I create a sample to have a test. Here I suggest you to transform your table in Power Query Editor, then you can get result by creating a matrix visual.

      M code:

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZZBLDoAgDETv0jWXEBHlt9aEsHSp3n8nHyMtXRAyvOl0QowwgYD9uc473z6fokNVh4ckIsz4QYDs8jOoIaEPhMrlwBVL2P6XwhfGNa2gUWBbsZIRgXTjOLIa8MZiMKSBgeEPLMGWFXS0oBvnPeWeBQRqIDKlFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Team Member #2 Name" = _t, #"Team Member #2 Shirt Style" = _t, #"Team Member #2 Size" = _t, #"Team Member #3 Name" = _t, #"Team Member #3 Shirt Style" = _t, #"Team Member #3 Size" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Team Member #2 Name", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Duplicated Column" = Table.DuplicateColumn(#"Unpivoted Other Columns", "Attribute", "Attribute - Copy"),
    #"Extracted First Characters" = Table.TransformColumns(#"Duplicated Column", {{"Attribute", each Text.Start(_, 14), type text}}),
    #"Extracted Text Range" = Table.TransformColumns(#"Extracted First Characters", {{"Attribute - Copy", each Text.Middle(_, 15, 100), type text}}),
    #"Pivoted Column" = Table.Pivot(#"Extracted Text Range", List.Distinct(#"Extracted Text Range"[#"Attribute - Copy"]), "Attribute - Copy", "Value"),
    #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Attribute", Order.Ascending}, {"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns"

      New Table:

      Then create a matrix and get result directly.

      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.

       

       

      • tockert's avatar
        tockert
        Regular Visitor
        4 years ago

        Thanks for the help.  

        This worked.  I was able to use this as a template to get it to work for all the team columns, as I have a total of 5 people per team.  

        I now have one matrix with all the shirt style and size counts.  Saves us from having to manually count this up.