Forum Discussion

KHSK's avatar
KHSK
Icon for Advocate I rankAdvocate I
9 months ago
Solved

Need Power Query code to load unique IDs in the snapshot history Table

Extremely sorry for the confusion, I just updated the TO-BE table. Logic: If approval dates are same for VP& SVP for an ID, I need any one of them to be displayed uniquely. If approval dates ...
Need Help
  • Chewdata's avatar
    Chewdata
    9 months ago

    Hello,

    The code below might get the right results. See annotations in the code

    let
    Source = YOURDATA,

    // group the data by ID and snapshotdate combination
    groupby_id_snapshotdate = Table.Group(
        Source, 
        {"ID", "SnapshotDt"}, 
        {{"Table", each _, type table [ID=nullable number, ApprovalDate=nullable date, ApprovalRole=nullable text, Status=nullable text, SnapshotDt=nullable date]}}),

    // determine which row from the nested table should be returned
    add_SelectedRow = Table.AddColumn(
        groupby_id_snapshotdate, 
        "SelectedRow", 
        // if there is only one row, return the row
        each if Table.RowCount([Table]) = 1 then [Table]{0} 
        
        // if the list of approvaldates is distinct that means there is more than one date in the list.
        else if List.IsDistinct([Table][ApprovalDate])

            // return te row with the max date
            then Table.SelectRows([Table], (row) => row[ApprovalDate] = List.Max([Table][ApprovalDate])){0} // get 

            // the list is not distinct = all dates are the same -> return first row
            else Table.First([Table])),

    // get the data from the records
    expand_records = Table.ExpandRecordColumn(
        add_SelectedRow, 
        "SelectedRow", 
        {"ApprovalDate", "ApprovalRole", "Status"}, 
        {"ApprovalDate", "ApprovalRole", "Status"}),
    
    // remove the table column
    removeColumns = Table.RemoveColumns(
        expand_records,{
            "Table"})
in
    removeColumns

     

     

Praful_Potphode's avatar
Praful_Potphode
Icon for Super User rankSuper User
9 months ago

Hi KHSK ,

 

Try below:

let
    // Step 1: Load Excel file from local path
    Source = Excel.Workbook(File.Contents("C:\Path\To\Your\File.xlsx"), null, true),

    // Step 2: Access the correct sheet or named table
    RawData = Source{[Item="SnapshotHistory", Kind="Sheet"]}[Data],

    // Step 3: Promote headers and set data types
    PromotedHeaders = Table.PromoteHeaders(RawData, [PromoteAllScalars=true]),
    Typed = Table.TransformColumnTypes(PromotedHeaders, {
        {"Id", Int64.Type},
        {"ApprovalDate", type date},
        {"ApprovalRole", type text},
        {"Status", type text},
        {"SnapshotDt", type date}
    }),

    // Step 4: Get latest ApprovalDate per SnapshotDt
    MaxDates = Table.Group(Typed, {"SnapshotDt"}, {
        {"MaxApprovalDate", each List.Max([ApprovalDate]), type date}
    }),

    // Step 5: Merge MaxDates back to main table
    Merged = Table.NestedJoin(Typed, "SnapshotDt", MaxDates, "SnapshotDt", "MaxJoin", JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(Merged, "MaxJoin", {"MaxApprovalDate"}),

    // Step 6: Replace ApprovalDate for VP rows
    UpdatedDates = Table.AddColumn(Expanded, "FinalApprovalDate", each 
        if [ApprovalRole] = "VP" then [MaxApprovalDate] else [ApprovalDate], type date),

    // Step 7: Clean up columns
    RemovedOld = Table.RemoveColumns(UpdatedDates, {"ApprovalDate", "MaxApprovalDate"}),
    Renamed = Table.RenameColumns(RemovedOld, {{"FinalApprovalDate", "ApprovalDate"}}),

    // Optional: Sort for readability
    Sorted = Table.Sort(Renamed, {{"SnapshotDt", Order.Ascending}, {"ApprovalRole", Order.Ascending}})
in
    Sorted

replace step1,2,3 as per your source connector.

Sample PBIX and excel FIle

Please give kudos or mark it as solution once confirmed.

 

Thanks and Regards,

Praful

 

KHSK's avatar
KHSK
Icon for Advocate I rankAdvocate I
9 months ago

Hey Praful,Please find the updated logic & the TO-BE table.