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
Anonymous
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
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
PivotednilendraFabric , 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