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

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

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

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.

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.

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.

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

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.

Hi KHSK ,
Did you get a chance to look at the response Chewdata  shared? It seems valuable to review. Let us know if you need more details or changes.

Thank you Chewdata .