How to do unpivot multiple sheets of Attendance

[Daniel_JD]

Hi all,

I have attendance data in Excel in the following way.

Sheets: January 2023, February 2023, etc..

In each sheet, 

ID	Name	2-Oct-23	3-Oct-23	4-Oct-23	5-Oct-23	6-Oct-23
		Monday	Tuesday	Wednesday	Thursday	Friday
1	Birdie	PL	PL	6:00 AM - 3:00 PM	6:00 AM - 3:00 PM	6:00 AM - 3:00 PM
2	Abbi	PL	6:00 AM - 3:00 PM	6:00 AM - 3:00 PM	1:30 PM - 10:30 PM	6:00 AM - 3:00 PM
3	Carina	6:00 AM - 3:00 PM	6:00 AM - 3:00 PM	6:00 AM - 3:00 PM	6:00 AM - 3:00 PM	6:00 AM - 3:00 PM

I required the format,

Emp ID	Name	Date	Shift
1	Birdie	2-Oct-23	PL
1	Birdie	3-Oct-23	PL
1	Birdie	4-Oct-23	6:00 AM - 3:00 PM
1	Birdie	5-Oct-23	6:00 AM - 3:00 PM
1	Birdie	6-Oct-23	6:00 AM - 3:00 PM
2	Abbi	2-Oct-23	PL
2	Abbi	3-Oct-23	6:00 AM - 3:00 PM
2	Abbi	4-Oct-23	6:00 AM - 3:00 PM
2	Abbi	5-Oct-23	1:30 PM - 10:30 PM
2	Abbi	6-Oct-23	6:00 AM - 3:00 PM
3	Carina	2-Oct-23	6:00 AM - 3:00 PM
3	Carina	3-Oct-23	6:00 AM - 3:00 PM
3	Carina	4-Oct-23	6:00 AM - 3:00 PM
3	Carina	5-Oct-23	6:00 AM - 3:00 PM
3	Carina	6-Oct-23	6:00 AM - 3:00 PM

Kindly help me to figure out the solution for the power query in Power BI.

Thanks in advance.

[Anonymous · Accepted solution]

Hi @Daniel_JD 

You can refer to the follwing solution.

1.You can ceeate a blank query , then put the following code to advanced editor in power query

(A as table)=>
let
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(A, {"ID", "Name"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Date"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}})
in
    #"Changed Type1"

Set it as a function.

2.Then import other tables and use the funtion.

You can refer to the following video and attachments.

Bing Videos

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Daniel_JD]

Thanks, @Anonymous, this is helpful. One quick question do I need to refer each month manually?

[Anonymous]

Hi @Daniel_JD 

You need to import the tables, and then just put the table to the function. then it will be transformed automically.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Daniel_JD]

If the files are separate as shown in the video we can refer to the table. but mine has a single file with multiple sheets. guild to fix it!

[Anonymous]

Hi @Daniel_JD 

You can import one sheet first, then tranform it and set it as the function, then import other sheets in the file, and use the function.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mlsx4]

Hi @Daniel_JD 

1. First of all, you should append all your sheets in a unique file: Follow this tutorial and instead of removing empty rows (you may not have), filter by ID <>null (to remove the rows that have Monday, Tuesday...) https://www.youtube.com/watch?v=gQo5coD9c4M
2. Once you have combined all files: select the column ID and Name, go to Transform tab> Unpivot other column

[Daniel_JD]

Thanks @mlsx4,

I tried the same, and when I combined the sheets the dates were on the first row, so I can't make it as the header. (if I did the dates on the next sheet will be ignored).

On the video, they have the dates on the first column but in my case, it is in the first row in all sheets.

Further suggestions are welcome..

[mlsx4]

Hi @Daniel_JD 

You're right. Probably you can follow the steps in this solution to combine: https://community.fabric.microsoft.com/t5/Power-Query/Automatically-import-multiple-Excel-files-by-M...

I mean, you apply first transformations to each file and then combine

[Daniel_JD]

I have checked that, which will be applicable when the files are separated mine is a single file with a different sheet. 

Please correct me if I did not understand that solution properly!

[mlsx4]

You're right @Daniel_JD 

I don't have any more ideas (apart from doing manually). Probably Yolo Zhu's solution will work!!

[Daniel_JD]

Thanks @mlsx4.