Help On Power Query to transform table data

[Anonymous]

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	Assembly	Clutch
5678	XYZ	Assembly	Brake
9876	ASD	Casting	Engine
9876	ASD	Casting	Wheel

Need to have the output as below. Can you please help. 

Emp Code	Emp Name	Emp Dept	Part1	Part2	Part3
1234	ABC	Painting	Door	Hood
5678	XYZ	Assembly	Engine	Clutch	Brake
9876	ASD	Casting	Engine	Wheel

[dufoq3]

Hi @Anonymous, similar approach:

Output

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]),
    Transformed = Table.Combine(Table.Group(Source, {"Emp Code"}, {{"T", each List.Accumulate({0..List.Count([Part])-1}, Table.RemoveColumns(Table.FirstN(_, 1), {"Part"}), (s,c)=>
        Table.AddColumn(s, "Part" & Text.From(c+1), (x)=> [Part]{c}, type text)), type table}})[T])
in
    Transformed

[AlienSx · Accepted solution]

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

[Anonymous]

Thanks @AlienSx , It worked perfectly

[Akash_Varuna]

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

[Anonymous]

Thanks @Akash_Varuna 

Tried the above. Pivot failing on nested columns.

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]),
#"Grouped Rows" = Table.Group(Source, {"Part"}, {{"All Parts", each _, type table [Emp Code=nullable text, Emp Name=nullable text, Emp Dept=nullable text, Part=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([All Parts], "Index", 1, 1, Int64.Type)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Emp Code", "Emp Name", "Emp Dept", "Part", "Index"}, {"Custom.Emp Code", "Custom.Emp Name", "Custom.Emp Dept", "Custom.Part", "Custom.Index"}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Custom", {{"Custom.Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expanded Custom", {{"Custom.Index", type text}}, "en-US")[Custom.Index]), "Custom.Index", "Part")
in
#"Pivoted Column"

[Akash_Varuna]

@Anonymous I think your grouping is caysing that please try this
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]),
#"Grouped Rows" = Table.Group(Source, {"Emp Code", "Emp Name", "Emp Dept"}, {{"All Parts", each _, type table [Emp Code=nullable text, Emp Name=nullable text, Emp Dept=nullable text, Part=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([All Parts], "Index", 1, 1, Int64.Type)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Part", "Index"}),
#"Pivoted Column" = Table.Pivot(#"Expanded Custom", List.Distinct(#"Expanded Custom"[Index]), "Index", "Part"),
#"Renamed Columns" = Table.RenameColumns(#"Pivoted Column", {{"1", "Part1"}, {"2", "Part2"}, {"3", "Part3"}})
in
#"Renamed Columns"

[Anonymous]

Thanks @Akash_Varuna 

Tried the above code. Its failing on Pivot.