Solution to Crispo Mwangi's question on Dec 31 23

ChallengeURL.txt

https://www.linkedin.com/posts/crispo-mwangi-6ab49453_excel-exceltips-crispexcel-activity-7147058784119554048-Am6l?utm_source=share&utm_medium=member_desktop

pq-crispexcel-20231231-Allocations.m

// Query Name: Allocations
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TimeType = Table.TransformColumnTypes(Source,{{"TIME", type time}}),
    Unpivot = Table.UnpivotOtherColumns(TimeType, {"TIME","JOBS"},"DATE","Staff")
in
    Unpivot

pq-crispexcel-20231231-fnGetAllocations.m

// Function Name: fnGetAllocations
(Name as text) as table =>
let
    // Get the rows for the requested name
    Filter = Table.SelectRows(Allocations, each [Staff] = Name),
    // Convert the time to text and pad with leading zeros
    TimeAsText = Table.TransformColumnTypes(Filter,{{"TIME", type text}}),
    PadTime = Table.TransformColumns(TimeAsText, {{"TIME", each Text.PadStart(_, 8, "0")}}),
    // Apply the desired sort order
    SortDateTime = Table.Sort(PadTime,{{"DATE", Order.Ascending}, {"TIME", Order.Ascending}}),
    // Combine the date, time and job into a single column
    Merge = Table.CombineColumns(
        SortDateTime,
        {"JOBS", "DATE", "TIME"},
        Combiner.CombineTextByDelimiter(", "),
        "Date Time & Job"),
in
    // Position the Staff column as the first column
    Table.ReorderColumns(Merge,{"Staff", "Date Time & Job"})