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.
Community SupportHi, 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.
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
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.