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

[KHSK]

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 are different then I need the ID with the Max Date to be retrieved.

Basically, I need unique ID's in the TO-BE table, doesn't matter if it is VP or SVP...I need one record per ID to show that it is approved.

My Snapshot history table is like the AS-IS table above but I need it to be transformed to look like the TO-BE table above.

This I need to do at the Power Query level not at the DAX level. Appreciate your help.

[Chewdata]

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

[V-yubandi-msft]

Hi @KHSK ,
Could you let us know if your issue has been resolved or if you are still experiencing difficulties? Your feedback is valuable to the community and can help others facing similar problems.

[KHSK]

Hi All,

Apologize for the late response, none of the solutions yielded the expected result I wanted. 

Thanks everyone for your inputs.

[V-yubandi-msft]

Hi @KHSK ,

I wanted to check if you had the opportunity to review the information provided by @Ahmedx @Ashish_Mathur @vojtechsima . Please feel free to contact us if you have any further questions. 
 

Thank you and continue using Microsoft Fabric Community Forum.

[Ahmedx]

pls try

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIw1jMw1zMyMDIFcsICgIRjQUFRfllqCkjSQg8oD5aM1cGiPpiABiOQkAlciKAFGOoJWUCMDwwNSPQBsgZifIBXPV4LYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, ApprovalDate = _t, ApprovalRole = _t, Status = _t, SnapshotDt = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"ApprovalDate", type date}, {"ApprovalRole", type text}, {"Status", type text}, {"SnapshotDt", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Id"}, {{"tmp",
 (x)=>[
  a = Table.SelectRows(x , each [ApprovalDate] = List.Max(x[ApprovalDate])),
  b = Table.FirstN(a,1)][b]

}},0),
    Custom1 = Table.Combine( #"Grouped Rows"[tmp])
in
    Custom1

[Ashish_Mathur]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Id"}, {{"Count", each Table.Max(_,"ApprovalDate")}},GroupKind.Local),
    #"Expanded Count" = Table.ExpandRecordColumn(#"Grouped Rows", "Count", {"ApprovalDate", "ApprovalRole", "Status", "SnapshotDt"}, {"ApprovalDate", "ApprovalRole", "Status", "SnapshotDt"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Count",{{"ApprovalDate", type date}, {"SnapshotDt", type date}, {"Id", type text}})
in
    #"Changed Type1"

Hope this helps.

[vojtechsima]

hey, @KHSK ,
please nex time share your logic, I did try to figure it out here:

try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIw1jMw1zMyMDIFcsICgIRjQUFRfllqCkjSQg8oD5aM1cGiPpiABiOQkAlciKAFMPUmxFpAjA8MDUj0AbIGYnyATT1eHyBrMAYJmRJvgQm6erwWxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, ApprovalDate = _t, ApprovalRole = _t, Status = _t, SnapshotDt = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"ApprovalDate", type date}, {"ApprovalRole", type text}, {"Status", type text}, {"SnapshotDt", type date}}),
    sortRows = Table.Sort(#"Changed Type",{{"SnapshotDt", Order.Descending}, {"Id", Order.Ascending}, {"ApprovalDate", Order.Descending}}),
    group = Table.Group(sortRows, {"Id", "SnapshotDt"}, {{"rows", (tbl)=> let
        latestApproval = List.Max(tbl[ApprovalDate]),
        rows = Table.SelectRows( tbl, each [ApprovalDate] = latestApproval),
        check = if Table.RowCount( rows ) > 1 then Table.SelectRows(rows, each [ApprovalRole] = "VP" ) else rows
        in check

}}),
    expand = Table.ExpandTableColumn(group, "rows", {"ApprovalDate", "ApprovalRole", "Status"}, {"ApprovalDate", "ApprovalRole", "Status"})
in
    expand

Replace Source and Changed Types to yours steps. And adjust the sort at the end, but the logic is there.

[KHSK]

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

[Praful_Potphode]

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]

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

[vojtechsima]

Hey @Praful_Potphode , doesn't really work. Did you test it?

[ribisht17]

HI @KHSK 

Is this your logic?

For each

Id, ApprovalRole and
SnapshotDt
, you retain only one row

But more specifically, for VP roles, you keep the row with the same ApprovalDate across snapshots.
For SVP roles, you prefer the latest ApprovalDate per Id, even if it’s not tied to the latest SnapshotDt.

What is the logic here?

Regards,

Ritesh

Community Champion

Please mark the answer if helpful so that it can help others

Dance-Sing with Data -BI & Analytics

[KHSK]

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