Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
I have a data coming from our HR tool like this:
Name | Start date of absence | End date of absence |
John Doe | 01/10/2018 | 02/10/2018 |
Jane Doe | 02/10/2018 | 05/10/2018 |
I would like to generate a new table using the information above like the following:
Name | Absence date |
John Doe | 01/10/2018 |
John Doe | 02/10/2018 |
Jane Doe | 02/10/2018 |
Jane Doe | 03/10/2018 |
Jane Doe | 04/10/2018 |
Jane Doe | 05/10/2018 |
Do you have any thoughts on how to convert this data?
Thanks!
Ugur
Solved! Go to Solution.
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
See file attached as well
@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!
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
See file attached as well
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?
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |