Need Help on Power Query to transform 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

[v-sgandrathi]

Hi @Anonymous,

Hope you are doing well.

Thanks for connecting with the Microsoft Fabric Community Forum.

we haven't heard back from you regarding the last response and wanted to check if your issue has been resolved.

If our response addressed by the community member for your query, please mark it as Accept Answer and give us Kudos if you found it helpful.

Should you have any further questions, feel free to reach out.

Thank you, @nilendraFabric for your prompt response to the query.

Regards,
Sahasra.

[nilendraFabric]

@Anonymous 

Give it a try 

let
Source = Excel.CurrentWorkbook(){[Name="YourTableName"]}[Content],
GroupedRows = Table.Group(
Source,
{"Emp Code", "Emp Name", "Emp Dept"},
{{"Rows", each _, type table [Emp Code=nullable text, Emp Name=nullable text, Emp Dept=nullable text, Part=nullable text]}}
),
AddIndex = Table.AddColumn(
GroupedRows,
"IndexedRows",
each Table.AddIndexColumn([Rows], "PartIndex", 1, 1)
),
ExpandedRows = Table.ExpandTableColumn(
AddIndex,
"IndexedRows",
{"Part", "PartIndex"},
{"Part", "PartIndex"}
),
Pivoted = Table.Pivot(
ExpandedRows,
List.Distinct(ExpandedRows[PartIndex]),
"PartIndex",
"Part"
)
in
Pivoted

[Anonymous]

Thanks @nilendraFabric 

It worked fine till here

Giving an error when expanding with Index

[nilendraFabric]

@Anonymous 

Try this

let
Source = Excel.CurrentWorkbook(){[Name="YourTableName"]}[Content],
GroupedRows = Table.Group(
Source,
{"Emp Code", "Emp Name", "Emp Dept"},
{{"Rows", each _, type table [Emp Code=nullable text, Emp Name=nullable text, Emp Dept=nullable text, Part=nullable text]}}
),
AddIndex = Table.AddColumn(
GroupedRows,
"IndexedRows",
each Table.AddIndexColumn([Rows], "PartIndex", 1, 1)
),
ExpandedRows = Table.ExpandTableColumn(
AddIndex,
"IndexedRows",
{"Part", "PartIndex"},
{"Part", "PartIndex"}
),
ConvertedRows = Table.TransformColumnTypes(ExpandedRows, {{"PartIndex", type text}}),
Pivoted = Table.Pivot(
ConvertedRows,
List.Distinct(ConvertedRows[PartIndex]),
"PartIndex",
"Part"
)
in
Pivoted

[Anonymous]

Thanks @nilendraFabric 

Pivot is not allowing over the nested Index Column

[nilendraFabric]

let
// Source Data
Source = Excel.CurrentWorkbook(){[Name="YourTableName"]}[Content],

// Group by Employee Details
GroupedRows = Table.Group(
Source,
{"Emp Code", "Emp Name", "Emp Dept"},
{{"Rows", each _, type table [Emp Code=nullable text, Emp Name=nullable text, Emp Dept=nullable text, Part=nullable text]}}
),

// Add Index Column Within Each Group
AddIndex = Table.AddColumn(
GroupedRows,
"IndexedRows",
each Table.AddIndexColumn([Rows], "PartIndex", 1, 1)
),

// Expand Indexed Rows
ExpandedRows = Table.ExpandTableColumn(
AddIndex,
"IndexedRows",
{"Part", "PartIndex"}
),

// Pivot Part Column Based on PartIndex
Pivoted = Table.Pivot(
ExpandedRows,
List.Distinct(ExpandedRows[PartIndex]),
"PartIndex",
"Part"
)
in
Pivoted

[Anonymous]

Thanks @nilendraFabric 

Still, it doesn't seem to work.

[Anonymous · Accepted solution]

@nilendraFabric , The below worked perfectly. Thanks

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

[v-sgandrathi]

Hello @Anonymous,

I'm glad you found a solution and resloved  the query. Thank you very much for sharing here.

Kindly mark your reply as the accepted solution so that others in the community can find it quickly.

Thankyou for connecting with Microsoft Community Forum.