Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Help On Power Query to transform table data

I have a data as below after joining 2 tables.    Emp Code Emp Name Emp Dept Part 1234 ABC Painting Door 1234 ABC Painting Hood 5678 XYZ Assembly Engine 5678 XYZ Assemb...
Need Help
  • AlienSx's avatar
    AlienSx
    1 year ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUXJ0cgaSAYmZeSWZeelApkt+fpFSrA5uBR75+SlgBaZm5hZAfkRkFEhZcXFqblJOJZDpmpeemZeKV4lzTmlJcgZeJU5FidkQQywtzM1AcsEuIJ2JxVB3IFmDQ0V4RmpqjlJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Emp Code" = _t, #"Emp Name" = _t, #"Emp Dept" = _t, Part = _t]),
    group = Table.Group(Source, {"Emp Code", "Emp Name", "Emp Dept"}, {"Part", (x) => x[Part]}),
    part_cols = List.Transform({1..List.Max(List.Transform(group[Part], List.Count))}, (x) => "Part " & Text.From(x)),
    split = Table.SplitColumn(group, "Part", (x) => x, part_cols)
in
    split
Akash_Varuna's avatar
Akash_Varuna
Icon for Super User rankSuper User
1 year ago

Hi Anonymous , Please try this 

  • Group Data:

    • Go to the Home tab, click Group By.
    • Group by Emp Code, Emp Name, and Emp Dept.
    • Create a new column(All Parts), and set the operation to All Rows.

  • Add Custom Columns:

    • Click on the small gear icon in the All Parts column and choose only the Part column.
    • Add a Custom Column:
      Table.AddIndexColumn([All Parts], "Index", 1, 1, Int64.Type)
    • Expand the column to flatten the table.

  • Pivot Columns:

    • Select the Index column and use the Pivot Column feature.
    • Set the values column to Part.

  • Rename Columns:

    • Rename the new columns to Part1, Part2, based on their position.
      If this post helped please do give a kudos and accept this as a solution 
      Thanks In Advance