Forum Discussion
Helper VPivot...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
Helper VThe timestamp is just not available. And, yes there are multiple entries in the history that occur on the same date. My co-worker created multiple queries in Access. We created a mother table. Then 2 temp tables with all the Start Dates (NewValues) & EndDates(OldValues)-he said he had some code that was going to index these tables. Then we run another make table query that temporarily holds the min value from each of these tables, then another query appends that to the mother table. Then queries to delete the values in the temp tables. There is a function that loops through the tables until it finishes. I haven't seen the code he developed to index the tables, so I am curious to see if it works.
Then he has more vba code to calculate the business day difference which literally takes about an hour to update a table with over 69K rows. And that is just one of the tables.
Monday is going to suck.
Super UserChallenge them on the timestamp. It is clearly available in SFDC. It must get lost somewhere along the process. Worst case use CSV extracts instead (via DataLoader or even reports) to get that. It would drastically cut down on the processing duration, and reduce the ambiguity.