Forum Discussion
Help On Power Query to transform table data
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
Super UserHi 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"
- 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"