cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ugurgulluev Regular Visitor
Regular Visitor

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

Accepted Solutions
Super User
Super User

Re: Extracting dates from date ranges

@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

View solution in original post

5 REPLIES 5
Super User
Super User

Re: Extracting dates from date ranges

@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

View solution in original post

Re: Extracting dates from date ranges

@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! 

ugurgulluev Regular Visitor
Regular Visitor

Re: Extracting dates from date ranges

Hi @Zubair_Muhammad, That worked really well! Thanks!

Chemiefrik Frequent Visitor
Frequent Visitor

Re: Extracting dates from date ranges

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?

Highlighted
Johnanass015 Occasional Visitor
Occasional Visitor

Re: Extracting dates from date ranges

how to do this on custom tables?

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 369 members 3,790 guests
Please welcome our newest community members: