Forum Discussion
Solved
Pivot...UnPivot??? How to fix this bad data setup
I have a table called App Hx. It looks like this: HxId ParentId CreatedDate OldValue NewValue 0178y00006qrgdC aAB8y0000004dK6GAI 4/1/2024 Created Submitted 0178y000068XMB2 aAB...
Use this. That line was not needed.
let Source =Table.FromRows(Excel.CurrentWorkbook(){[Name="Table2"]}[Content]), Custom1 = [a = List.Skip(Table.ToColumns(Source)), b = {a{0}} & {a{3}} & {a{1}} & {List.Skip(a{1}) & {null}}, c = Table.FromColumns(b, {"Parent ID", "Event","Start Date", "End Date"}) ][c] in Custom1I think what yoy are asking for is result for a group of Parent ID. Then use this
let Source =Table.FromRows(Excel.CurrentWorkbook(){[Name="Table2"]}[Content]), Custom1 = Table.Combine(Table.Group(Source, {"ParentId"}, {"All", each [a = List.Skip(Table.ToColumns(_)), b = {a{0}} & {a{3}} & {a{1}} & {List.Skip(a{1}) & {null}}, c = Table.FromColumns(b, {"Parent ID", "Event","Start Date", "End Date"}) ][c]})[All]) in Custom1
Use this. Source below is a sample one only.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjA0t6g0AAKzwqL0FGclHaVERyeIiIGBSYq3mbujJ1DQRN9Q38jAyATIdC5KTSxJTQGygkuTcjNLQOxYHWSTLCJ8nYxwm2QEMwmhX0cpKLW4ID+vOFUhKLWwNLMIi5l+xRl4XGcAMxTTINwONY4oS07BY6gpOS41jigyS8Tje3JcalrlGFFUhsdQQ2wuDShK1XUsKCjKL8MwL78ypyyMGPNQzNBRQhgXCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [HxId = _t, ParentId = _t, CreatedDate = _t, OldValue = _t, NewValue = _t]),
Custom1 = [a = List.Skip(Table.ToColumns(Source)), b = {a{0}} & {a{3}} & {a{1}} & {List.Skip(a{1}) & {null}}, c = Table.FromColumns(b, {"Parent ID", "Event","Start Date", "End Date"}) ][c]
in
Custom1
I must be doing something wrong because I get a error of Token Idenfitie expected at the 2nd let. This is what I have:
let Source =Table.FromRows(Excel.CurrentWorkbook(){[Name="Table2"]}[Content]), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [HxId = _t, ParentId = _t, CreatedDate = _t, OldValue = _t, NewValue = _t]), Custom1 = [a = List.Skip(Table.ToColumns(Source)), b = {a{0}} & {a{3}} & {a{1}} & {List.Skip(a{1}) & {null}}, c = Table.FromColumns(b, {"Parent ID", "Event","Start Date", "End Date"}) ][c] in Custom1Use this. That line was not needed.
let Source =Table.FromRows(Excel.CurrentWorkbook(){[Name="Table2"]}[Content]), Custom1 = [a = List.Skip(Table.ToColumns(Source)), b = {a{0}} & {a{3}} & {a{1}} & {List.Skip(a{1}) & {null}}, c = Table.FromColumns(b, {"Parent ID", "Event","Start Date", "End Date"}) ][c] in Custom1Oh wow. You may have just saved us hours of work. Thank you so very much for sharing.
