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. 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
Custom1
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 Custom1I really hate that I have to report this, but it is still not giving me the proper values. Example:
Id ParentId CreatedDate OldValue NewValue 0178y00006YNsBmAAL aAB8y0000000b6BGAQ 11/20/2023 Denied Submitted 0178y00006ZfHleAAF aAB8y0000000b6BGAQ 11/22/2023 Submitted Response Required 0178y00006ZrGuVAAV aAB8y0000000b6BGAQ 11/22/2023 Response Reqired Submitted 0178y00006bjYj5AAE aAB8y0000000b6BGAQ 11/27/2023 Submitted Approved Code I used (edited to filter data for all records with a specific ParentId to test results:
let Source = Access.Database(File.Contents("C:\Users\scastille539\AppData\NA_APP_TIMELINES\NA_App_Timeline.accdb"), [CreateNavigationProperties=true]), _tblNAHistory = Source{[Schema="",Item="tblNAHistory"]}[Data], #"Sorted Rows" = Table.Sort(_tblNAHistory,{{"Id", Order.Ascending}, {"CreatedDate", Order.Ascending}}), #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"CreatedById"}), #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [ParentId] = "aAB8y0000000b6BGAQ"), #"Sorted Rows1" = Table.Sort(#"Filtered Rows",{{"CreatedDate", Order.Ascending}, {"Id", Order.Ascending}}), Custom1 = Table.Combine(Table.Group(#"Sorted Rows1", {"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 Custom1Result - it is completely wrong 😞
Parent Id Event Start Date End Date aAB8y0000000b6BGAQ Submitted 11/20/2023 11/27/2023 aAB8y0000000b6BGAQ Approved 11/27/2023 11/22/2023 aAB8y0000000b6BGAQ Submitted 11/22/2023 11/22/2023 aAB8y0000000b6BGAQ Response Required 11/22/2023 null My co-worker has created an incredibly complicated series of queries and vba code in MS Access. I wanted to avoid using it. But since I can't seem to get this to work, I have no choice.
Thank for trying to help. Your patience is commendable.
Maybe now you are ready to follow my recommendations? We've been through the same exercise before in our company and have successfully solved it.
