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
The Object ID is 18 digits. The only time we get 15 digit object ids are when we are connecting to a SF Report. In this instance, we are connecting directly to the Object.
The Created Date does not contain a valid time - they are all 12:00:00 AM.
I do not know the total rows in the Application object, but it is 100's of thousands.
Same for the History Object. I think even after I filtered the Field column for only Application Status, it was still over 800k.
See if you can get the timestamp directly from the Application Field History object. Without it you will have a very hard time sorting the events. If multiple events happened on the same day you would not be able to determine the cadence.
800K is considered small. Remember that the Field history data is immutable so you don't have to repull old data over and over - you can run a process that only fetches the changes since the last time you looked. Note: Salesforce Object connector queries do actually fold if you limit yourself to simple filters - they will produce custom SOQL. That means you could use Incremental Refresh if you were using Power BI Service. But even with Access you can implement something similar.
For the Application object itself you could do a query with the Last Modified Date to reduce the amount of data to pull, and then a script in Access to swap out the rows that have changed and add the rows for new applications.
You need the Application object for cases where there is no entry in the Application Field History object.
The 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.
Challenge 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.