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