Forum Discussion
Count values in the same cell which are not separated
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.
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!
Community SupportHi, 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.