Expand grouped data but apply a custom column value only to the last group record

[Richard_Halsall]

Hi

I am attempting to apply a custom column value only to the last group record, [I have a column named 'SiteDate' which could be used to determine the last record] before expanding my data view is as shown below with 'MinimumHours' a custom column

So if I expand the 'AllData' column the 'MinimumHours' value will be against each record

However I only require it to show against the last record in the group in this instance the line with a 'SiteDate' of 05/01/2025

Can this be achieved in PQ any assistance would be appreciated

Thanks

[Akash_Varuna · Accepted solution]

Hi @Richard_Halsall Could you try this please 

• Group the data by "Technician" and include all rows in a column named "AllData".
• Add an index column to each group to track row order.
• Add a column to identify the last record in each group by comparing the index to the total row count.
• Add a custom column that assigns MinimumHours only to the last record in each group; otherwise, assign null .
• Expand the grouped table back into rows with the added columns.
• Try this code please replace according to your columns

let
    GroupedData = Table.Group(Source, {"Technician"}, {{"AllData", each _, type table [SiteDate=date, MinimumHours=number]}}),
    
    // Add Index to Each Group
    AddIndex = Table.TransformColumns(GroupedData, {"AllData", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type)}),

    // Identify Last Record
    AddIsLastRecord = Table.TransformColumns(AddIndex, {"AllData", each Table.AddColumn(_, "IsLastRecord", (row) => row[Index] = Table.RowCount(_))}),

    // Apply Custom Column for Last Record
    AddCustomColumn = Table.TransformColumns(AddIsLastRecord, {"AllData", each Table.AddColumn(_, "CustomValue", (row) => if row[IsLastRecord] then row[MinimumHours] else null)}),

    // Expand Back Data
    ExpandedData = Table.ExpandTableColumn(AddCustomColumn, "AllData", {"SiteDate", "MinimumHours", "CustomValue"})
in
    ExpandedData

[Richard_Halsall]

@Akash_Varuna Perfect thanks very much