Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I am trying to load the Resource Capacity table from Business Central into PBI, in order to create some metrics around resource utilization.
The data in this table is stuctured in an awkward way, with one row for each day starting at 01/01/0001, for each resource. I'd like to filter to dates this year, but I'm getting an error whenever I try to filter this dataset:
It seems like any filters I try to apply creates a timeout error with the business central connection.
Is there any way around this? I know Power BI can handle datasets of this size.
Thanks!
Solved! Go to Solution.
HI @lachlanP,
it sounds like you want to do some operation before getting data to power bi desktop side, right? if that is the case, I'd like to suggest you switch to different data connectors with query options. (e.g. OData connector) Then you can get data with the additional query filter to reduce the response data amounts.
OData Web Services - Business Central | Microsoft Docs
Regards,
Xiaoxin Sheng
does the 01/01/0001 correspond to an actual date? if yes, what would that date be? It seems that in general it should be possible to handle this date in M:
https://docs.microsoft.com/en-us/powerquery-m/sharpdate
but I would expect issues when loading to DAX
@Stachu , thanks for the reply.
Yes, it does correspond to an actual date and I can create a simple date filter in M:
The problem is when I apply the filter (press ok), the system loads and loads and then I get the error that the web service timed out
so it really relates to the year one in the gregorian calendar? or is it a placeholder for null or there is some other logic behind it?
@Stachu ,
Yes, it relates to year one. I suppose it could be considered a placeholder. Basically this table is showing resource availability for every resource for every day (since the begining of time...ish). Of course none of this data is relevant or populated until recent dates, for me I only care about this year. The problem is that over 99.9% of this data (rows full of zeros) needs to get filtered out, but the connection breaks before I can do this.
In Business Central the system processes this into a matrix and you perform queries within the system to get the info you need. I think I am going to need to write and publish a custom query to expose just the portion of this dataset that I care about.
HI @lachlanP,
it sounds like you want to do some operation before getting data to power bi desktop side, right? if that is the case, I'd like to suggest you switch to different data connectors with query options. (e.g. OData connector) Then you can get data with the additional query filter to reduce the response data amounts.
OData Web Services - Business Central | Microsoft Docs
Regards,
Xiaoxin Sheng
Microsoft themselves explicetly state in Business Central to consider using API request instead of OData.
to the OP: have you tried to save a new report and using filtering rules in BC directly ?
Under asssisted setup there as point in "Repirt on financial health" (don´t knwo why its not translated as I switched to english for this screenshot)
using this option lets you choose data sources in BC (pages and existing queries - so all you need is the page objct ID, afaik all page objects seem available for selection, even from 3rd party extensions ) , choose the fields to be included , then you can apply additional filters.
this report gets automatically published under web services and thus can be used via the legacy business central data source connector
maybe that helps out?
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |