Staff roster from table to a list

Hi Inawab770 

PowerBI Query code:

let
    Source = Excel.Workbook(
        File.Contents("D:\Lorenzo\Downloads\Staff Roster.xlsx"),
        null, true
    ),
    SheetOne = Source{[Item="Sheet1", Kind="Sheet"]}[Data],
    PromotedHeaders = Table.PromoteHeaders(SheetOne, [PromoteAllScalars=true]),
    ColumnNames = Table.ColumnNames(PromotedHeaders),
    RequiredColumns = Table.SelectColumns(PromotedHeaders,
        List.FirstN(ColumnNames, List.PositionOf(ColumnNames, "CITY"))
    ),
    UnpivotedOtherColumns = Table.UnpivotOtherColumns(RequiredColumns, {"Name"}, "Date", "Shift"),
    DatesAsNumbers = Table.TransformColumns(UnpivotedOtherColumns,
        {"Date", Number.From, Int64.Type}
    ),
    ChangedTypes = Table.TransformColumnTypes(DatesAsNumbers,
        {{"Name", type text}, {"Date", type date}, {"Shift", type text}}
    )
in
    ChangedTypes

NB: Update line File.Contents("D:\Lorenzo\Downloads\Staff Roster.xlsx") with your path to the file

Assumptions made:

- Roster is on sheet named Sheet1 as in the file you shared
- Column [Name] is always the 1st column as in the file you shared
- Column [CITY] is always the 1st column after the Date columns as in the file you shared

The position of the latter is used to determine the number of Date columns (not all months have 31 days)

Attached file is zipped as .pbix isn't allowed