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.

9 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        Vijay_A_Verma
        Most Valuable Professional
        4 years ago

        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?

  • 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