Splitting a column

[Anonymous]

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.

[BA_Pete]

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

[Anonymous]

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.

[BA_Pete]

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

[Vijay_A_Verma]

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 

[Anonymous]

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]

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.

[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?

[Anonymous]

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 

[Anonymous · Accepted solution]

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.