Forum Discussion
Splitting a column
Hi Anonymous ,
A solution in Power Query would be to:
add an Index column,
split the Q column in a new column,
expand this new column,
add a new column with prefix "V",
pivot ,
remove the Index columnlet Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKzCtJLdJRcEktzi7JL1AIcNZR8E6tTMpPLErRUfDNLy1O1VHwSSxKTwVy8jJL8oFqHfMqFfxLMlKLFHQVggtSkzPTKhWSUnPyy5VidaKVUBSDRTDMC85NzMnBpwRNDE19LAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Device List" = _t]), Indexed = Table.AddIndexColumn(Source, "Index", 0, 1), Splitted = Table.AddColumn(Indexed, "Splitted", each Text.Split([Device List], ", ")), Expanded = Table.ExpandListColumn(Splitted, "Splitted"), Prefixed = Table.AddColumn(Expanded, "Inserted Prefix", each "V" & [Splitted], type text), #"Pivoted Column" = Table.Pivot(Prefixed, List.Distinct(Prefixed[#"Inserted Prefix"]), "Inserted Prefix", "Splitted"), #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"}) in #"Removed Columns"Reference: Solved: Split a cell values in a column to multiple column... - Microsoft Power BI Community
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Most Valuable ProfessionalWill need to see your data as solution may be as simple as split column to a complex one.
How to Get your Question Answered Quickly - https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
I want to create columns separate columns for keyboard,mouse,large monitor etc.
so i want to create separate columns for keyboard,mouse,large monitor etc.
- Vijay_A_Verma
Beneath those columns what do you want to be populated? For example, under Large monitor what would come? Will user name need to be populated below those columns?
Beneath the columns i should see the devices e.g keyboard column beneath it i want to see the keyboards.There is already a column for names
Hi Anonymous ,
A solution in Power Query would be to:
add an Index column,
split the Q column in a new column,
expand this new column,
add a new column with prefix "V",
pivot ,
remove the Index columnlet Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKzCtJLdJRcEktzi7JL1AIcNZR8E6tTMpPLErRUfDNLy1O1VHwSSxKTwVy8jJL8oFqHfMqFfxLMlKLFHQVggtSkzPTKhWSUnPyy5VidaKVUBSDRTDMC85NzMnBpwRNDE19LAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Device List" = _t]), Indexed = Table.AddIndexColumn(Source, "Index", 0, 1), Splitted = Table.AddColumn(Indexed, "Splitted", each Text.Split([Device List], ", ")), Expanded = Table.ExpandListColumn(Splitted, "Splitted"), Prefixed = Table.AddColumn(Expanded, "Inserted Prefix", each "V" & [Splitted], type text), #"Pivoted Column" = Table.Pivot(Prefixed, List.Distinct(Prefixed[#"Inserted Prefix"]), "Inserted Prefix", "Splitted"), #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"}) in #"Removed Columns"Reference: Solved: Split a cell values in a column to multiple column... - Microsoft Power BI Community
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Vijay_A_Verma
Need to look at some sample data to work out the steps for you. Please upload the file without confidential/sensitive data to a cloud storage service such as Onedrive/Google Drive/Dropbox/Box (Onedrive preferred) and share the link here.