Jun 30 2022 05:13 AM
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
Jun 30 2022 05:51 AM
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 OneDrive, Google Drive... and post the link here
Jul 02 2022 11:14 PM
Jul 03 2022 06:54 AM
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