Count values in the same cell which are not separated

[Anonymous]

I'm having an issue when getting the values to put it in the dashboard relating to quantity. The column "empresas" and also "CNPJ" have some single and double values that interfere in the final quantity relating the amount of "empresas" (companies) that I want to show in a card on the dashboard. 

I was trying to figure out some way of counting it with "containstring" but I have multiple different companies that I just can't type each one (and also don't think it will be the most viable way).

Does anyone know how can I count each company ?

(also considering that some companies are being shown more than once, I have to also "distinct count" it, because "Apple", "Apple; IBM", "Apple; Samsung; Uber" are different, so the analysis has to count 4 companies instead of 3 as shown in this example)

[v-angzheng-msft]

Hi, @Anonymous 

Mcode:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("5VPLjhshEPwV5LOnAwMzgHzCDPa0NAMWYG+U3f2M/f/0PNYrJZcoyi1Xurq6uqp5fT3U4MeIN3bOruDEGhbiNcTRZXRHhrFUNzmfQmGBuVvCxGYqVZwTmyoAHI6H3oAVHRgrvnHORaP44f1IxPhAz4g+pild0REBxkvKs/NuaR4cDZsDEQgB3HQgZbsRyHYleKSasos0i2DVYWEF3In9/vwDo09UW8QIA8pa6EW/cKmm1yemWlCih77bBXK98p+vLLx9cN72z+0XVQsLb6E3AoTptg69KXKlJI+r/oHsKAVLDZFemGOeDIveUeGCHkOs4cR8mm8ujlQ/590rDlZbUJ3eiIU4MSlBSAGd+pRn1mFhJlEhs30xrtmxNxZI7wYzdoXNbpzC6gTJD4+ww2mQ0j0YowgtdWM30kuI+J1yGO6lkuBljTCFmlNEsvC5PyVihQYtzZ6IWrtfwpUNGWkI24yjDSM1T6Vh7l7TV7QLhwLJLVi761WbgpcR83RL1AMLqrMgeEcoS6hON3I7nsu8ns643Q7NG9KvGSkDom2htXtGXG4ZZR8mujKsiyN7z+dx6KPqLWhOTgs6DtOeGK35fFuj1n9AA/8Dzb+c/PUTCg4h3/MV6a9E5zFFNxEXfQJO/1RLvqep/r7v/Sc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Empresas = _t, CNPJ = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Empresas", type text}, {"CNPJ", type text}}),

    
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Company", each Text.Split([Empresas],"; ")),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Company"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom", each Text.Split([CNPJ],"; ")),
    #"Expanded Custom1" = Table.ExpandListColumn(#"Added Custom1", "Custom"),
    #"Grouped Rows" = Table.Group(#"Expanded Custom1", {"Company"}, {{"Count", each Table.RowCount(Table.Distinct(_)), Int64.Type}})
in
    #"Grouped Rows"

Result:

Please refer to the attachment below for details.

Hope this helps.

If this doesn't work for you, could you please consider sharing more details about it and posting expected result so it is clear on what needs to be implemented?  And It would be great if there is a sample file without any sesentive information here.It makes it easier to give you a solution.

Best Regards,
Community Support Team _ Zeon Zheng

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

[Anonymous]

Hello!!

Thank you so much for your help. You don't know for how long i've been trying to figure this out. 

If I want to create another list from another data (Company, company area of activity and company size), do I need to add another step or can I just modify the M code that you sent here?

As for now, when I expand 2 lists, the company data is duplicated and I can't relate the other list ("Porte") to "Company".

But even so, I just want to thank you so much!

[v-angzheng-msft · Accepted solution]

Hi, @Anonymous 

Yes, the above results have duplicate values.
I tried to modify the code and use a simple sample to illustrate what I implemented.

Sample:

M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcrR2UtJRqrCutK5SitUB8oG8cjDLydoZyK6EiFq7ANllIDWxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Company = _t, Product = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Company", type text}, {"Product", type text}}),


    #"Added Custom" = Table.AddColumn(#"Changed Type", "Company_All", each Text.Split([Company],";")),
    #"Expanded Company_All" = Table.ExpandListColumn(#"Added Custom", "Company_All"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Company_All", "Custom", each Text.Split([Product],";")),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom1", "Custom"),


    #"Grouped Rows" = Table.Group(#"Expanded Custom", {"Company_All"}, {{"Count", each List.Count(List.Distinct(_[Custom]))}})
in
    #"Grouped Rows"

Result:

The above code can be modified as below and the result will be a distinct count.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("5VPLjhshEPwV5LOnAwMzgHzCDPa0NAMWYG+U3f2M/f/0PNYrJZcoyi1Xurq6uqp5fT3U4MeIN3bOruDEGhbiNcTRZXRHhrFUNzmfQmGBuVvCxGYqVZwTmyoAHI6H3oAVHRgrvnHORaP44f1IxPhAz4g+pild0REBxkvKs/NuaR4cDZsDEQgB3HQgZbsRyHYleKSasos0i2DVYWEF3In9/vwDo09UW8QIA8pa6EW/cKmm1yemWlCih77bBXK98p+vLLx9cN72z+0XVQsLb6E3AoTptg69KXKlJI+r/oHsKAVLDZFemGOeDIveUeGCHkOs4cR8mm8ujlQ/590rDlZbUJ3eiIU4MSlBSAGd+pRn1mFhJlEhs30xrtmxNxZI7wYzdoXNbpzC6gTJD4+ww2mQ0j0YowgtdWM30kuI+J1yGO6lkuBljTCFmlNEsvC5PyVihQYtzZ6IWrtfwpUNGWkI24yjDSM1T6Vh7l7TV7QLhwLJLVi761WbgpcR83RL1AMLqrMgeEcoS6hON3I7nsu8ns643Q7NG9KvGSkDom2htXtGXG4ZZR8mujKsiyN7z+dx6KPqLWhOTgs6DtOeGK35fFuj1n9AA/8Dzb+c/PUTCg4h3/MV6a9E5zFFNxEXfQJO/1RLvqep/r7v/Sc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Empresas = _t, CNPJ = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Empresas", type text}, {"CNPJ", type text}}),

    
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Company", each Text.Split([Empresas],"; ")),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Company"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom", each Text.Split([CNPJ],"; ")),
    #"Expanded Custom1" = Table.ExpandListColumn(#"Added Custom1", "Custom"),
    
    #"Grouped Rows" = Table.Group(#"Expanded Custom1", {"Company"}, {{"Count", each List.Count(List.Distinct(_[Custom]))}})
in
    #"Grouped Rows"

Please refer to the attachment below for details.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

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