Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Splitting a column

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.
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
BA_Pete
Icon for Super User rankSuper User
4 years ago

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