Forum Discussion
split to distinct columns
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
Hi Pat, the query did not work for me, could you please send pbix file including my excel file as input source?
Regards
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
Custom1
I am still curious what analyses/visuals you have planned. This data structure is likely not ideal.
Pat
Thanks Pat. You are right. Visualization will be easier with data unpivoted.
Regards
mahoneypat I think I have a similar type problem and was wondering if this technique could be adapted to work. I have a table with the last column "FailureModeMatches" that looks like this:
The numbers come from an index tied to a particular failure mode. I would like to generate a unique column for each index and label that column with the index number. The content of each column would be either "Yes" or null. In this example row 1 would have a "Yes" in column.index5 and in column.index36, but a null in all other columns. There is no set number of added columns other than what the data prescribes (currently about 40 columns, but may not be 1...40, and may expand as the index grows.
Thank you,
Paul
A similar approach could be applied where you use Text.Split to create a list from your text string. I would then expand those lists to new rows and keep the data unpivoted rather than create many new columns. It will make analysis easier and faster.
Pat
