Forum Discussion
Need Help on Power Query to transform 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 | 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 |
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
9 Replies
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
PivotedAnonymous
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
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.
