Forum Discussion
split to distinct columns
Hello Experts,
How can get a table from the following so that the new table shows separate group columns and in each column, the name of users belongs to that group?
I copied your data from the spreadsheet and put it in through the Enter Data approach. The query is below (and contains your data). You can create a blank query, open the Advanced Editor and then replace the text there with this.
The last two steps are customized. The #"Grouped Rows" keeps the Name column as a List (usually Group steps result in a table). The Custom1 step uses a function not used often called Table.FromColumns. It converts a list of lists into a table, and the 2nd parameter provides the column names as a list.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY5JDoNADAT/4nMfGJgAOYY1YckHRpz7/z8AW05G4lgld8kpyUsgDGAJVnIgSafiomjUK1XgA6zBxtygLl4EtuATDIX50VOOk09bozmP8uKd6+rNfbxyry/qM65+Fv9nwfzm79+q+69q9PWj2r47Tg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Groups = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Groups", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Split([Groups], ",")), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Groups"}), #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom"), #"Grouped Rows" = Table.Group(#"Expanded Custom", {"Custom"}, {{"All", each _[Name]}}), Custom1 = Table.FromColumns(#"Grouped Rows"[All], #"Grouped Rows"[Custom]) in Custom1I am still curious what analyses/visuals you have planned. This data structure is likely not ideal.
Pat
12 Replies
Here's one way to do it in the query editor. To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below. Not sure this is the best way to structure your data though. Your example data was an image, so I made a few rows of my own to test it out.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUUo31Ek30kk3VorViVZyAgnAec5QaQjPBSpnohQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Groups = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Groups", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Split([Groups], ",")), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Groups"}), #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom"), #"Grouped Rows" = Table.Group(#"Expanded Custom", {"Custom"}, {{"All", each _[Name]}}), Custom1 = Table.FromColumns(#"Grouped Rows"[All], #"Grouped Rows"[Custom]) in Custom1Pat
Pat
Hello and thanks Pat. Please let me check your query. Meanwhile could you please check the following file and let me know where is my problem?
https://drive.google.com/file/d/1rwxcXKohf5QjcJg8E06W3tcgIwkf8xcw/view?usp=sharing
