Forum Discussion

Ugarte335's avatar
Ugarte335
Copper Contributor
Nov 18, 2019
Solved

How to count multiple values in a cell

Hello,   This is the first time I post!   I need to do the following. I have a dataset and one of the fields is made up of cells that can have multiple values. For example the below. We have cell...
count
excel
Pivot Table Default
  • JKPieterse's avatar
    JKPieterse
    Nov 18, 2019

    Ugarte335 I used Powerquery to massage your data as you can see in the attached file. From the green table it'll be easy to create your pivottable report. You can also feed the result of the query into the pivottable directly without using the intermediate green table of course.

Ugarte335's avatar
Ugarte335
Copper Contributor
Nov 18, 2019

Thank you, this was much easier than I expected!

  • Ugarte335's avatar
    Ugarte335
    Copper Contributor
    Nov 19, 2019

    Thank you so much for your helpSergeiBaklan

     

    However, I tried with the data downloaded from the provider and it does not recognise it as new line for some reason. The delimiter with the data I provided first was a new line and it was recognised by power query.

     

    Now it doesn't. I have attached the spreadsheet for your convenience.

     

    Thanks

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Nov 19, 2019

      Ugarte335 

      It works with your file, please checked attached. I converted the data into the table to simplify, by that doesn't matter. You may use range or query sheet from another file.

      Generated script is

      let
    Source = Excel.CurrentWorkbook(){[Name="tblNames"]}[Content],
    #"Added Custom" = Table.AddColumn(
        Source,
        "Name",
        each Text.Split([Names],"#(lf)")
    ),
    #"Expanded Custom" = Table.ExpandListColumn(
        #"Added Custom",
        "Name"
    ),
    #"Removed Other Columns" = Table.SelectColumns(
        #"Expanded Custom",
        {"Country", "Name"}
    )
in
    #"Removed Other Columns"

Resources