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.

BA_Pete's avatar
BA_Pete
Super User
4 years ago

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'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
      Super 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