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
ugurgulluev
Helper II
Helper II

Extracting dates from date ranges

Hi all,

 

I have a data coming from our HR tool like this:

 

NameStart date of absenceEnd date of absence
John Doe01/10/201802/10/2018
Jane Doe02/10/201805/10/2018

 

I would like to generate a new table using the information above like the following:

 

NameAbsence date
John Doe01/10/2018
John Doe02/10/2018
Jane Doe02/10/2018
Jane Doe03/10/2018
Jane Doe04/10/2018
Jane Doe05/10/2018

 

Do you have any thoughts on how to convert this data?

 

Thanks!

Ugur

1 ACCEPTED SOLUTION
Zubair_Muhammad
Community Champion
Community Champion

@ugurgulluev

 

In the Query Editor..... Add a custom column as follows and convert it to date format

 

It will give you a list of dates from start to end.

 

 

{Number.From([Start date of absence])..Number.From([End date of absence])}

Then expand the list to new rows

listofdates.png

 

 

See file attached as well


Regards
Zubair

Please try my custom visuals

View solution in original post

5 REPLIES 5
henriquesilveir
Resolver IV
Resolver IV

@ugurgulluev hello, you can use the Unpivot table, it will turn 2 columns into 1 column, take a look in this quickly video, and learn how to turn the number of your columns your need in 1.

 

Please, don' forget to mark this post as solution if you got it! other people can have the same situation in the future! 

Zubair_Muhammad
Community Champion
Community Champion

@ugurgulluev

 

In the Query Editor..... Add a custom column as follows and convert it to date format

 

It will give you a list of dates from start to end.

 

 

{Number.From([Start date of absence])..Number.From([End date of absence])}

Then expand the list to new rows

listofdates.png

 

 

See file attached as well


Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

how to do this on custom tables?

Ist there also a formular for doing it the other way around.

So to get a Coloumn for each day to start and end date?

Hi @Zubair_Muhammad, That worked really well! Thanks!

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.