Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Scaffnull
Helper I
Helper I

Date table (Pivot, unpivot?)

Hi everyone,

I have a question about a date table where I'd like to format so I can get total count of people and total hours per date. 
I got handled (picture, and filter the peoples names). So the week column continues until present week.

Datetable.png

I wonder if anyone has a solution to fix dates for this. So example week 22 monday (May 31st)  the total count of name is 8 and total hours 59. I've tried to invoke a date table, but it doesn't recognize that monday week 22 is that date and so on.

 

Thanks for any sort of help.

1 ACCEPTED SOLUTION
edhans
Super User
Super User

You need a date table, and your weeks need to have year indicators. Otherwise once you get 2 or more years, week 22 will be in different years.

When I get source data that only has info like this, adding the year is pretty easy. As for the date, you can use this method. It returns a table like this, that you can now create a relationship with a date table, and do your aggregations.

edhans_0-1640036771731.png

I did this in Excel, so Open up Power Query and look at the Table query.

Here is my file.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

3 REPLIES 3
edhans
Super User
Super User

You need a date table, and your weeks need to have year indicators. Otherwise once you get 2 or more years, week 22 will be in different years.

When I get source data that only has info like this, adding the year is pretty easy. As for the date, you can use this method. It returns a table like this, that you can now create a relationship with a date table, and do your aggregations.

edhans_0-1640036771731.png

I did this in Excel, so Open up Power Query and look at the Table query.

Here is my file.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

@edhans Thank you for your reply. It worked, I created an empty query and imported a date table there in the advanced query, made the connection to the data table. I thought date for date was necessary but just kept it simple and displayed everything by weeks 🙂 

Great @Scaffnull - glad I was able to assist.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors