Error in setting up the dashboard for attendance involving multiple excel sheets in a workbook

[Jeyeline]

Hello Team,

 We are creating an attendance dashboard using an excel source file (ref: https://www.linkedin.com/pulse/data-analysis-using-power-bi-hr-domain-syed-abrar-muhtasim/) . It consists of several sheets each shows the data of a month. The column headers are name, and days in month.

After loading these into powerbi, we could profile the data upto one month perfectly. However, the second and third month data follows on the row, instead it has to be on the column header as mentioned in the below screenshot.

Can anyone help on this?

[v-denglli-msft]

Hi @ Jeyeline,

Based on the screenshot you provided, you are having trouble displaying multiple months of attendance information in a single Power BI table?

If so, I have not encountered your problem in my testing.

The sample data I created in a sheet in Excel is below.

The column headers are Name and Days in July and August.

Load into Power BI by way of Enter data and transform the data in Power Query.

Based on the link you provided, check the Name column, and then choose to /Unpivot Other Columns/.

You can convert the column containing the date from text to date.

Please see the attached PBIX for reference.

Best Regards,

Dengliang Li

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

[AnkitKukreja]

Hi! @Jeyeline 

Please ensure all the sheets have the same number of columns and match the names. According to the link, it seems straight forward, please try to follow those instructions and it should work.

[Jeyeline]

Hello @AnkitKukreja / @v-denglli-msft 

Every sheet follows same pattern. If you could understand the link, could you please assist on it.

1. We do have the dataset as in the link- months datasheet with same column(no of days)

2. We followed the steps and completed, but facing an issue where all the data from the sheet are fetched. Hence the column headers from the second sheet onwards lies in the datamodel. how to clean it up to have the dates alone in the column header just like the first month header. It is mentioned in the above screenshot.

Hope the problem statement is clear

[AnkitKukreja]

Hi! @Jeyeline 

I think I got your point now, in query editor filter your column and choose does not contain and type Emp No as in Data.column 2. This will remove that particular row everytime you load the new data.

Hope this will help, please let me know if I still misunderstood your problem.

[Jeyeline]

Hello @AnkitKukreja ,

 Thanks for your suggestion. But removing that line, will remove the date field across it. It has the data for 12 months which is in separate sheet. Each sheet has the same title which contains the number of days/dates for that particular month. Hence, removing that row eventually removed the days as well.

But for the dashboard, the days are mandatory. Could you help us on this?

[AnkitKukreja]

Hi! @Jeyeline 
I would request you share sample excel sheet with me, please remove PII data and I can give it a try.

[AnkitKukreja]

Hi! @Jeyeline 

There is no way I can copy that data from the link that you shared, please share the excel or copy paste the data in case the issue persists.

[Jeyeline]

Hello @AnkitKukreja ,

 Can you help me on this, how would i share the excel sheet as I could not upload the spreadsheet here either in zipped format or as actual file

[AnkitKukreja]

@Jeyeline 

You should see the option below. Else you can share link from your google drive or sharepoint for the same.

[Jeyeline]

Hello @AnkitKukreja ,

 I tried that, but it shows error prompt and restricted to add xlsx and .zip. Is there any extension that it supports?

[AnkitKukreja]

@Jeyeline 

Are you expecting your data to look like 

or 

I am attaching the sample pbix. "All Data" table has combine data for all the months, please let me know if you need anything else on this.

[Jeyeline]

Hello @AnkitKukreja / @v-denglli-msft ,

 Thanks for your continuous support. I want to build a dashboard like this image.

Linkedin Source

In order to do this, i would like to build the data model where the employee details are mentioned in column1, the days for all months(each sheet has each month) maintained as individual columns. Refer the SS.

target Datamodel

[AnkitKukreja]

@Jeyeline 

This is the expectation? 

Please find attached pbix and look for "All data" table.

[Jeyeline]

Hello @AnkitKukreja ,

 Thanks for the support. I could complete this step, yet few more data cleaning is required to complete the entire step. I will complete and update

Regards,

Jeyeline

[AnkitKukreja]

@Jeyeline 

Please try to send it in in DM or try to share the link of your personal google drive/sharepoint

[Jeyeline]

Please find the link

Drive Link