Forum Discussion

Txtcher's avatar
Txtcher
Helper V
1 year ago
Solved

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 aAB...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    1 year ago

    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's avatar
    Vijay_A_Verma
    1 year ago

    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
lbendlin's avatar
lbendlin
Super User
1 year ago

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's avatar
    Txtcher
    Helper V
    1 year ago

    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's avatar
      lbendlin
      Super User
      1 year ago

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