Forum Discussion
Pivot...UnPivot??? How to fix this bad data setup
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 |
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
21 Replies
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 CaseSafeIDThank 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.
SFDC = Salesforce.com - that's where your data comes from.
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 Custom1I 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 Custom1Use 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
