Pivot...UnPivot??? How to fix this bad data setup

[Txtcher]

I have a table called App Hx. It looks like this:

HxId	ParentId	CreatedDate	OldValue	NewValue
0178y00006qrgdC	aAB8y0000004dK6GAI	4/1/2024	Created	Submitted
0178y000068XMB2	aAB8y0000004dK6GAI	4/2/2024	Submitted	Response Required
0178y000068XNsh	aAB8y0000004dK6GAI	4/10/2024	Response Required	Submitted
0178y000063Xvcd	aAB8y0000004dK6GAI	4/15/2024	Submitted	Response Required
0178y000063Xr6a	aAB8y0000004dK6GAI	4/20/2024	Response Required	Submitted
0178y00005zAXrv	aAB8y0000004dK6GAI	4/21/2024	Submitted	Pre-Approved
0178y00005oylvV	aAB8y0000004dK6GAI	4/21/2024	Pre-Approved	Approved

Question: Is there a way to turn the data above into a table like below in power query?

2nd Question: The table has over 800K rows. I am pretty sure, but would like confirmation: is this table is too big to perform this kind of feat in power query?

Parent ID	Event	Start Date	End Date
aAB8y0000004dK6GAI	Submitted	4/1/2024	4/2/2024
aAB8y0000004dK6GAI	Response Required	4/2/2024	4/10/2024
aAB8y0000004dK6GAI	Submitted	4/10/2024	4/15/2024
aAB8y0000004dK6GAI	Response Required	4/15/2024	4/20/2024
aAB8y0000004dK6GAI	Submitted	4/20/2024	4/21/2024
aAB8y0000004dK6GAI	Pre-Approved	4/21/2024	4/21/2024
aAB8y0000004dK6GAI	Approved	4/21/204

[Vijay_A_Verma · Accepted solution]

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

[Vijay_A_Verma]

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

[Txtcher]

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 Custom1

[Vijay_A_Verma · Accepted solution]

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

[Txtcher]

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.

[Vijay_A_Verma · Accepted solution]

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

[Txtcher]

I 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 Custom1

 Result - 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.

[lbendlin]

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.

[Txtcher]

Okay, Mr. Super User:  If I could follow your recommendation I would give it a try. But I do not have the faintest idea how to implement it. I am an advanced beginner (maybe intermediate) user of Power Query. And your replies have been well and truly way over my head.

And, from what I could make sense of, your solution involved getting the data directly from SF. I do not want to get all that data without filtering it. It is HUGE.

We are currently pulling the SF object (application history) into Excel power query, and performing what filtering we can. Then an MS Access database links to the query. From there we created a make-table query to aggregate the data for the minimum Approved date. Then we use the new table to filter the original history (by ParentId) to only history items for applications with an approved date in the history.

It is from this point we need to figure out the dates an Application was in Submitted Status (start and end dates), and calculate the business days for each event.

Why are we using Ms Access and Excel and not Power BI? Because we work for state govenment. We just got Power BI this year. None of us are skilled enough to use it or had any formal training with it. I have only dabbled with Excel's data model and have created some simple DAX expressions and power pivots.

So here we are jumping through hoops using the ancient tools we have to move giant data.

The requestor wants a table of raw data along with the summary. And it has to be in Excel format.

Columns:

- App No

- App Type

- First Submitted Date

- Final Submitted Date

- Time in Submitted Status (this is where we need to create a table of Start and End Dates to determine the number of business days between each event and then total those sums.)

There are more columns, but it is pointless to go on any further.

So, if you have any helpful to add, it would be appreciated. In the meantime, please do not scold me. I did not intentionally ignore you. I just did not understand wth you are asking me to do.

[lbendlin]

Sorry to hear about you having to use MSAccess but it may actually turn out to be an advantage later on.

First step:  You must use the CaseSafeID for the Application object.  The 15 digit ID is not sufficient

Second:  Your Created Date must include the complete timestamp, including the seconds and (if available) the milliseconds.

Is that something you can do? Also, what is "huge" ?  how many rows in the Application object and how many rows in the Application History object?

[Txtcher]

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.

[lbendlin]

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.

[Txtcher]

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.

[Txtcher]

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? 

[Vijay_A_Verma]

What is the output expected from the sample you just posted? Accordingly, I can change the code.

[Txtcher]

Hello, 

I think I figured it out. Before I insert code you provided, I need to have the table sorted in in Hx Id  and CreatedDate order.

Example:

This was the table straight from source:

After adding custom step - note the null value next to Response Required it should be 11/22/2023 12:00:00 AM, not null

After inserting a step to sort the data by ascending CreatedDate and Id, I get this after the Custom Step, which is the correct version of the history:

So thank you so much. All is well.

[lbendlin]

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

[Txtcher]

Oh wow. You may have just saved us hours of work. Thank you so very much for sharing.

[lbendlin]

1. The SFDC Field History data is perfect as it is. No need to pivot or unpivot

2. NOTE : !!! NOTE : Both SFDC and Power Query are case sensitive. Power BI IS NOT case sensitive. If you cannot get the CaseSafeID from SFDC you MUST create it yourself in Power Query.

= (ObjectID as text) as text => let AN = "ABCDEFGHIJKLMNOPQRSTUVWXYZ", NFTC = (pos) => Number.From(Text.Contains(AN, Text.At(ObjectID,pos))), FirstIndex = NFTC(0) + NFTC(1)*2 + NFTC(2)*4 + NFTC(3)*8 + NFTC(4)*16, SecondIndex = NFTC(5) + NFTC(6)*2 + NFTC(7)*4 + NFTC(8)*8 + NFTC(9)*16, ThirdIndex = NFTC(10) + NFTC(11)*2 + NFTC(12)*4 + NFTC(13)*8 + NFTC(14)*16, ANP = AN & "012345", CaseSafeID = ObjectID & Text.At(ANP, FirstIndex) & Text.At(ANP, SecondIndex) & Text.At(ANP, ThirdIndex) in CaseSafeID

[Txtcher]

Thank  you for your response. Unfortunately, I can't make any sense of it. What is SFDC? And why is the history perfect as it? The code makes no sense to me either. What does it do?

We need to calculated the cumulative time an Application was in a given status.

My apologies but you completely lost me.

[lbendlin]

SFDC = Salesforce.com - that's where your data comes from.