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

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
Icon for Most Valuable Professional rankMost 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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      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.