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
Most Valuable ProfessionalI 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 VI 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.
- lbendlin
Super User
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?
