Forum Discussion

Inawab770's avatar
Inawab770
Copper Contributor
Jun 30, 2022

Staff roster from table to a list

Hi everyone,

 

I have the below Staff roster 

 

Tha date at top row, the names on the first caulome, in the table the shifts for each staff member.

 

I need to make this table as a list all in caulomes:

 

Staff Name - Date - Shift

 

this is so I can take itto power BI

 

is there a way to do this automated ? a formela or a power quri ?

or can I do this in power BI??

 

thank you

 

BI & Data Analysis
excel
power bi

3 Replies

Sort By
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Jun 30, 2022

    Hi Inawab770 

    If the following example reflects what you expect you can get this from PowerBI (it includes Power Query)

     

    If you need a Power BI working example please share a roaster workbook with i.e https://support.microsoft.com/en-us/office/share-onedrive-files-and-folders-9fcc2f7d-de0c-4cec-93b0-a82024800c07, Google Drive... and post the link here

    • Inawab770's avatar
      Inawab770
      Copper Contributor
      Jul 03, 2022
      Hi Lorenzo

      Thank you very much for your help, you can find google drive link below

      https://drive.google.com/drive/folders/1CCTq2BTr7xDCB5tZKcVNBbzxzSQekEJB?usp=sharing

      once again thank you for your help
      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        Jul 03, 2022

        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

Resources