cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ottiphi
Helper I
Helper I

Count of names within one column (several names in one cell), sorted by number od entries

I have a column with names in it. Additionally in each cell are mutiple names. Separated with a comma (semicolon possible as well). How can a generate a list within power bi with all names, sorted by number of entries.

 

The result should be:

Mixing: 4

industrie 4.0: 2

Resins: 1

....

 

Please see file attached!

 

Thank you for your help!

 

Hashtags konsolidiert
 
 
 
Mixing, Resins
 
Industrie 4.0
 
 
anchor, Mixing
Dispensing, Mixing
 
Industrie 4.0
spray, coating, Foam
Perforation, Customization, Firestop
Mixing, alternative geometry, Pastrymasses, Groutings dissolver
10 REPLIES 10
vanessafvg
Super User
Super User

@ottiphi

i copied and pasted  your data into the enter data in power query, basically what you need to do is copy from #changed type once you have copied and pasted into your power bi file.  otherwise tell me where to email to.

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZBBDoJADEWv0rCeGBfeQIO6MCFuCYsGKjTClLQDEU/viC4wxE2Tvvb/3zTPkxNaE7A2uIs3abli0pAULk9gVS/8YF87uJKxt8Xg7KvBgjLBbrNdydCXjaiDj3xGB7aevM1uC/zPzXrFyUEpGGZJKtjNg4z0JhqpeAf7qJKOn982ZaUI+p/TsQ2kPm6MBDVJR0GjcYYxb+rQjMzBUWV45xhUbPEnI2lSFC8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t]),


#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [#"(blank)"] <> null and [#"(blank)"] <> ""),
#"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "(blank)", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"(blank).1", "(blank).2", "(blank).3", "(blank).4"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {}, "Attribute", "Value"),
#"Replaced Value" = Table.ReplaceValue(#"Unpivoted Columns"," ","",Replacer.ReplaceText,{"Attribute", "Value"}),
#"Filtered Rows1" = Table.SelectRows(#"Replaced Value", each [Value] <> null and [Value] <> ""),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Attribute"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"Value"}, {{"Count", each Table.RowCount(_), type number}})
in
#"Grouped Rows"





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Hey,

 

thanks a lot for your quick answer. Unfortunately I have problems following you. Please E-mail to: philipp.ottink@hilti.com

 

Best regards

@ottiphi hi sorry unable to do his from work i can do it tonight 

 

however all you need to do is

in power bi in power query 

 

from new source select blank query

then select advanced editor from the home menu

in the text box that comes up copy the text below and replace all the text in there with 

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZBBDoJADEWv0rCeGBfeQIO6MCFuCYsGKjTClLQDEU/viC4wxE2Tvvb/3zTPkxNaE7A2uIs3abli0pAULk9gVS/8YF87uJKxt8Xg7KvBgjLBbrNdydCXjaiDj3xGB7aevM1uC/zPzXrFyUEpGGZJKtjNg4z0JhqpeAf7qJKOn982ZaUI+p/TsQ2kPm6MBDVJR0GjcYYxb+rQjMzBUWV45xhUbPEnI2lSFC8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [#"(blank)"] <> null and [#"(blank)"] <> ""),
#"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "(blank)", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"(blank).1", "(blank).2", "(blank).3", "(blank).4"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {}, "Attribute", "Value"),
#"Replaced Value" = Table.ReplaceValue(#"Unpivoted Columns"," ","",Replacer.ReplaceText,{"Attribute", "Value"}),
#"Filtered Rows1" = Table.SelectRows(#"Replaced Value", each [Value] <> null and [Value] <> ""),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Attribute"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"Value"}, {{"Count", each Table.RowCount(_), type number}})
in
#"Grouped Rows"

 

you should then have your data





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Hello,

 

tnak you for your support. It would help me a lot, when you send me your solution via email tonight. I have to admit, that I am not very familiar with power BI, yet (and with programming in general). The sample column I sent you is part of an table. How do I acces this specific column within the code? Please see the attachment...

 

Ah the code worked right away! There is one issue though. The name of the column is "Hashtags konolidiert". Can you manage, that the name of the column won't be counted? Also, what happens, when I have more than 4 words in one cell, let's say 20 (people can enter the words into the cells individually). I don't think they will enter more than 5, but maybe the one person will enter 13. I'd like to inhibit, that the code won't wok afterwards anymore.

 

Also (please see Screenshot 3), I'd like to have an interaktive visualization. Means, that when I click on the hashtag bar "mixing", in the table below will only be displayed the 4 idea lines with the hashtag "mixing". Currently, with the new querry, the hashtags are not linked to the ideas anymore.

 

Thanks a lot and best regards

 

 

@ottiphi

 

try replace the previous code with this  below

it will

1) put the column header in the right place

2) put a cap on how many columns , ive done 5, i suggest you see the procedural steps in the GUI of power query its created in power query to understand what it is doing

3) for creating interaction between the 2 tables, you need to create a relationship between these two tables however i see you have hashtags 1 - 6, are you wanting them to in this order are you just wanting to link hashtags to each other?  what are you trying to do.

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dVBLCsJADL1K6HoQFW+g+FkIxW3pIthYg+2kJNNiPb3j6EIpbh7k5X1IiiLbo10D1gY38SYNV0wastIVGUzwyHf2tYMTGXv7Whx81VtQJljN5hMb+vNV1MHbnqgNW0feUtoX/S/NOsXRwVkwJMtWsHWBLCwSLpMoJ72IRoV4B+uYIC0/PuOWNeqk+zkDm0Dqo2IgqElaChpLcozdY4tmZA52Kv2r06Bii/8ZSLOyfAI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "(blank)", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"(blank).1", "(blank).2", "(blank).3", "(blank).4", "(blank).5"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {}, "Attribute", "Value"),
#"Capitalized Each Word" = Table.TransformColumns(#"Unpivoted Columns",{{"Value", Text.Proper, type text}}),
#"Filtered Rows" = Table.SelectRows(#"Capitalized Each Word", each [Value] <> null and [Value] <> ""),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribute"}),
#"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"Value", Text.Trim, type text}}),
#"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Value", Text.Clean, type text}}),
#"Filtered Rows2" = Table.SelectRows(#"Cleaned Text", each [Value] <> null and [Value] <> ""),
#"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows2", [PromoteAllScalars=true]),
#"Grouped Rows" = Table.Group(#"Promoted Headers", {"Hashtags Konsolidiert"}, {{"No of Tags", each Table.RowCount(_), type number}})
in
#"Grouped Rows"





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Hello Vanessa,

 

thank you so much. One line represents an idea. The columns show all information abou the idea(s). You can ignore the columns Hashtag 1 - 7. The only interesting column is the column "hashtags konsolidiert", which we ill rename later to just "hashtags". Each idea can be linked to, let's say up to 5 hashtags, which we we filled in one cell (separated with a comma). As you can see in Screenshot3, that I sent you via email, we want to further analyze the data. When I click on Mixing for example in the diagramm "number of hashtags", i'd like to the see in the diagramm "list of ideas" only 4 lines (4 ideas) that received the hashtag "mixing". The column I sent you yesterday is only an example! Is that more clear now? So yes, the hastags should be linked to the ideas and to each other probably as well.

 

Thank you so much!!

 

Best regards

Hello Vanessa,

 

I sent you an Email yesterday, since it's easier, if you want to attach documents. Can you help me with linking back the hashtags to the ideas as well?

 

Best regards,

Philipp

@ottiphi did you email me?  vanessafvg@gmail.com ? 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




I just sent you an inbox message!

I have a column with names in it. Additionally in each cell are mutiple names. Separated with a comma (semicolon possible as well). How can a generate a list within power bi with all names, sorted by number of entries.

 

The result should be:

Mixing: 4

industrie 4.0: 2

Resins: 1

....

 

Please see file attached!

 

Thank you for your help!

 

Hashtags konsolidiert
 
 
 
Mixing, Resins
 
Industrie 4.0
 
 
anchor, Mixing
Dispensing, Mixing
 
Industrie 4.0
spray, coating, Foam
Perforation, Customization, Firestop
Mixing, alternative geometry, Pastrymasses, Groutings dissolver

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.