Forum Discussion
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 | 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 |
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
7 Replies
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
- Rename the new columns to Part1, Part2, based on their position.
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"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"
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 splitThanks AlienSx , It worked perfectly
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
