Forum Discussion
Pivot...UnPivot??? How to fix this bad data setup
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.
After examining the data, I ran into issues. Unfortunately, the data is so messed up, I have null values in the new and old value columns. Sample:
Date OldValue NewValue 6/28/23 Submitted Incomplete 5/4/2024 Incomplete 6/10/2024 Withdrawn Is there a way to deal with that?
This is a very standard process with clear rules for identifying snapshot values
1. if there is no entry in the field history that means the current value is used
2. if there are entries in the field history before the snapshot date then use the New Value of the latest entry
3. if there are entries in the field history after the snapshot date then use the Old Value of the earliest entry
4. if the target field is blank then move on to the next extry
I really, really need help. Sorting the data by Id and CreatedDate did not help. I am getting very strange results. If I filter the data by a single ParentId before adding the code, then I get the expected results. But if I try to add the code to unfiltered data, something strange happens.
Sample Data:
Id ParentId CreatedDate OldValue NewValue 0178y00005LfiTpAAJ aAB8y0000004TjkGAE 07/12/2023 Incomplete Submitted 0178y00005cob9UAAQ aAB8y0000004TjkGAE 08/10/2023 Submitted PreApproved 0178y0000645cErAAI aAB8y0000004TjkGAE 10/04/2023 PreApproved Approved Expected Result:
Parent ID Event Start Date End Date aAB8y0000004TjkGAE Submitted 07/12/2023 08/10/2023 aAB8y0000004TjkGAE PreApproved 08/10/2023 10/04/2023 aAB8y0000004TjkGAE Approved 10/04/2023 null But here is what I get!
Parent ID Event Start Date End Date aAB8y0000004TjkGAE Submitted 07/12/2023 07/12/2023 aAB8y0000004TjkGAE PreApproved 08/10/2023 08/10/2023 aAB8y0000004TjkGAE Approved 10/04/2023 10/04/2023 Thank you so much for your help so far. But I really need more assistance. I have worked on this project for 2 weeks. I have to have something produced by Monday.
I 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
