Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi
I have a From date and To date columns with in a table but i want remaining dates between from date - to date for each and every employee id. Using calendar auto functions i have created date staticlly for all dates and also created data model connection but its not mapping as expected.
Regards,
Yuvaraj
Solved! Go to Solution.
Hi @Anonymous,
Based on my test, you could refer to below steps in query editor:
Sample data:
Selected the two columns and use the "Unpivot columns" function:
Now you could get the merged columns.
Regards,
Daniel He
Hi @Anonymous,
Based on my test, you could refer to below steps in query editor:
Sample data:
Selected the two columns and use the "Unpivot columns" function:
Now you could get the merged columns.
Regards,
Daniel He
@Anonymous
1. Click “Edit queries” to open Power Query editor
2. Make sure your date columns are date (not date/time) format
3. Create a custom column
4. Listofdays = { Number.From([From Date])..Number.From([ToDate])}
5. Click OK
6. Click the “diverging-arrow” icon next to “Date” and click “Expand to new rows”. Notice the addition of new rows.
7. Change the column type to “Date”
8. Click “Close and Apply”
Final Step Create Calculated
List of days = CONCATENATEX(FILTER(ALL(Tab),Table[Emplyee ID]=EARLIER([Emplyee ID])),[Listofdays]," , ")
Hi @chotu27
I tried to reproduce the steps that you mentionned il your comment
Could you please help with this error:
thanks in advance,
unique values falls duplicate.
without duplication how can i achieve.
@Anonymous you need this view right?
[To Date] - [From Date]
That will give you the number of days between the two dates.
Yes but in my report i need to create a date filter that date contains all date's. Here am expecting the date as one new column it contains all dates which have that from date and to date.
Regards,
Yuvaraj
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |