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.

[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.