Forum Discussion

JMST's avatar
JMST
Frequent Visitor
2 years ago
Solved

How to transform one column with grouped data in columns

Hi,

I have the following information charged in Power BI: 

The first column, contains the source filename, and the second one, contains data for several machines, each 10 lines correspond to 10 atributes for each machine, I want to move each atribute to one column, obtaining 10 diferent columns for each atribbute and each column have to store the values per each diffetent machine, for resume, I need to convert the raw data to this:

What I need to do to obtain it?

 

Thank you in advanced

 

Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi,

    Thanks for the solution lbendlin offered, and i want to offer some more information for user to refer to.

    hello JMST , you cna create a blank query  and put the following code to advanced editor in power query.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUQpOTszLS01RitVB4Svk5yl4leYoWOgoGBkYmSgYGFsByQBfuDrfxOSMzLxUQ7hARESEHhhFwIVyMvNKK0pKK+ACQUCDPRJLFFzzSlKLCooyi1MVfEBqFIJTi8pSixTM9Szhaovy80vgHIRwgJMznG2kkJtflIru9MHpFYTpZggzPMzhbEOoX2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Datos = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Datos", type text}}),
    #"Grouped Rows1" = Table.Group(#"Changed Type", {"Name"}, {{"Content", each List.Transform(List.Split(_[Datos],10),each Text.Combine(_,"/")),type list}}),
    #"Expanded Content" = Table.ExpandListColumn(#"Grouped Rows1", "Content"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Expanded Content", "Content", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Content.1", "Content.2", "Content.3", "Content.4", "Content.5", "Content.6", "Content.7", "Content.8", "Content.9", "Content.10"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Content.1", type text}, {"Content.2", type text}, {"Content.3", type text}, {"Content.4", type text}, {"Content.5", type text}, {"Content.6", type text}, {"Content.7", type text}, {"Content.8", Int64.Type}, {"Content.9", type text}, {"Content.10", type text}})
in
    #"Changed Type1"

    Output

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

5 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    Add two columns, one with an index modulo 10 and one with an index integer divide by 10.  Then pivot on the Datos column.

     

     

    • JMST's avatar
      JMST
      Frequent Visitor
      2 years ago

      Hi,

      Thanks for your solution, but It doesn't work, when I pivoted the data, the data is not ordered in the same column, it is ordered like a cascade.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi,

    Thanks for the solution lbendlin offered, and i want to offer some more information for user to refer to.

    hello JMST , you cna create a blank query  and put the following code to advanced editor in power query.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUQpOTszLS01RitVB4Svk5yl4leYoWOgoGBkYmSgYGFsByQBfuDrfxOSMzLxUQ7hARESEHhhFwIVyMvNKK0pKK+ACQUCDPRJLFFzzSlKLCooyi1MVfEBqFIJTi8pSixTM9Szhaovy80vgHIRwgJMznG2kkJtflIru9MHpFYTpZggzPMzhbEOoX2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Datos = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Datos", type text}}),
    #"Grouped Rows1" = Table.Group(#"Changed Type", {"Name"}, {{"Content", each List.Transform(List.Split(_[Datos],10),each Text.Combine(_,"/")),type list}}),
    #"Expanded Content" = Table.ExpandListColumn(#"Grouped Rows1", "Content"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Expanded Content", "Content", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Content.1", "Content.2", "Content.3", "Content.4", "Content.5", "Content.6", "Content.7", "Content.8", "Content.9", "Content.10"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Content.1", type text}, {"Content.2", type text}, {"Content.3", type text}, {"Content.4", type text}, {"Content.5", type text}, {"Content.6", type text}, {"Content.7", type text}, {"Content.8", Int64.Type}, {"Content.9", type text}, {"Content.10", type text}})
in
    #"Changed Type1"

    Output

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • JMST's avatar
      JMST
      Frequent Visitor
      2 years ago

      Hi,

       

      Thank you for your answer. You solved my problem.

      Thans