Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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 Assemb...
Need Help
  • AlienSx's avatar
    AlienSx
    1 year ago
    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
dufoq3's avatar
dufoq3
Icon for Community Champion rankCommunity Champion
1 year ago

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