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
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"
Super UserAnonymous 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"