The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Good morning - and a special good morning to the sleep deprived Game of Thrones watchers.
I am doing some work with Dynamics 365 Online and have solved the following;
1. Converting UTC Dates/times to AEST
2. Converting aformentioned dates/times to local taking into consideration Daylight saving
3. Calculating response times based on work hours, excluding weekends and (using a test table), removing public Holidays from the response times calculations.
You will note in 3 that I am using a test table to hold the holiday dates - I hate doing this because it is replicating data - I would like to get the data from the Dynamics calenders (then someone else can maintain it).
I am accessing Dynamics via https://{redacted}.api.crm6.dynamics.com/api/data/v9.1
I can access Calendar but the real data is in CalendarRules
When I try to get Calendarrules it hurls an error "The 'RetrieveMultiple' method does not support entities of type 'calendarrule'
It would seem to me that although the table is exposed by Dynamics, the API will not retrieve the data.
I am assuming that Dynamics will need to have an update to the api however I wonder if anyone has managed to use deep magic to retrieve the table?
Note that on our on-premise installation, using SQL Server I can access exactly the data I want!
Stan
Solved! Go to Solution.
Actually forget all the previous - have a much easier method.
1. Get data from your dynamics online instance as normal.
2. Select Calendars
3. Wait for Preview
4. Tick the tick box and then hit [edit] - not [load]
5. Find the calender and look for the column calendar_Calendar_Rules
6. Click on the word Table
7. new query will be created blah blah blah, rename etc!
Hi @Anonymous
Currently Dynamics 365 Web API still does not support GET Request for Calendar Rule entity,
Calendar Rule Entity is not avaiable in Power BI.
Best Regards
Maggie
Thanx Maggie- I understand about the get method - just inquiring if anyone has managed to get calendar rule via other black magic methods.
Stan
OK - I have managed to achive what I wanted to do.
Essentially I want the data for a calendar called 'SLA Public Holidays'
How I did it.
Set up an Odata feed as follows;
[get data] - Odata Feed.
Tick advanced
enter URL as
https://{redacted}.api.crm6.dynamics.com/api/data/v9.1/
2nd part is
calendars?$select=name&$expand=calendar_calendar_rules
Click [Ok]
After preview comes back, hit Edit
Then I click the Table entry next to my SLA Public Holidays Calendar in the list of tables returned and a new query is created.
For cleanliness, I rename this new query 'SLAPublicHolidays' and hit close and apply.
Lo and behold I have the details of my SLA Public Holidays calendar!
I ended up with a single table called 'SLAPublicHolidays' that I can extract the EffectiveIntervalStart and the name to get all the public holidays entered into the SLA Public Holidays calendar!
Stan
Actually forget all the previous - have a much easier method.
1. Get data from your dynamics online instance as normal.
2. Select Calendars
3. Wait for Preview
4. Tick the tick box and then hit [edit] - not [load]
5. Find the calender and look for the column calendar_Calendar_Rules
6. Click on the word Table
7. new query will be created blah blah blah, rename etc!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
95 | |
93 | |
85 | |
68 | |
65 |
User | Count |
---|---|
241 | |
124 | |
120 | |
81 | |
79 |