topic Re: Splitting a column in Power Query

Splitting a column

Anonymous — Wed, 23 Mar 2022 09:12:58 GMT

I want to split a column into multiple columns e.g i want to create a column for each of keyboard,desktop,Mouse,dongle so that i can view wat each user has to their disposal.

Re: Splitting a column

Vijay_A_Verma — Wed, 23 Mar 2022 09:29:59 GMT

Will 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

Re: Splitting a column

BA_Pete — Wed, 23 Mar 2022 11:39:55 GMT

Hi Anonymous ,

Select the column you want to split in Power Query.

Go to Transform tab > Split Column (dropdown) > By Delimiter. As per your example, use comma as the delimiter.

Pete

Re: Splitting a column

Anonymous — Wed, 23 Mar 2022 12:15:10 GMT

so i want to create separate columns for keyboard,mouse,large monitor etc.

Re: Splitting a column

Anonymous — Wed, 23 Mar 2022 12:17:46 GMT

So i want to create separate columns for large monitor,small monitor,keyboard etc.,how do i go about it since they are many different characters in one cell.

Re: Splitting a column

Vijay_A_Verma — Wed, 23 Mar 2022 12:51:31 GMT

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?

Re: Splitting a column

BA_Pete — Wed, 23 Mar 2022 12:57:50 GMT

Hi Anonymous ,

I would recommend splitting out your individual devices, but keeping them in one column. This is the most efficient and best-practice format for reporting data.

Paste this ver the default code in a new blank query in Power Query:

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUSooyswrSS3SUUhJLc4uyS9QKEjWUchOrUzKTyxKUYrViVYyAqrKSSxKT1XIzc/LLMkHqs3NLy1OBUsaAyWRdRbnJubkYCiMBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [user = _t, deviceList = _t]), addDeviceCol = Table.AddColumn(Source, "device", each Text.Split([deviceList], ", ")), expandDeviceCol = Table.ExpandListColumn(addDeviceCol, "device") in expandDeviceCol

Once you have the data in this structure, you can use a matrix visual to get the devices into columns.

Pete

Re: Splitting a column

Anonymous — Wed, 23 Mar 2022 13:11:15 GMT

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

Re: Splitting a column

Vijay_A_Verma — Wed, 23 Mar 2022 13:44:34 GMT

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.

Re: Splitting a column

Anonymous — Mon, 28 Mar 2022 09:06:18 GMT

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 column

let 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.