Forum Discussion
Power BI Service data refresh due to data type error + Power BI Desktop Error applying Power Query
Maybe you can describe which reports you want to run off the data. A lot of your transforms do not serve a real purpose (sorting rows or rearranging columns for example). If you cut out all the unnecessary transforms you get the best performance.
Not sure if you need both the timestamp and the date but you can do a double dipping with the standard converter.
let
Source = OData.Feed("https://data.seattle.gov/api/odata/v4/tazs-3rd5", null, [Implementation="2.0"]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"report_datetime", type datetime}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"report_datetime", type date}})
in
#"Changed Type1"
Hello lbendlin , thanks so much for replying to my query.
I'll be honest that I'm quite a novice with Power BI and I honestly don't know what your suggestion means. The Power BI Desktop seems like it's simply timing out around the 500MB mark after applying the data model update (parsing report_datetime). I guess I could just forget about report_datetime and re-publish the rest of the file minus one of the visuals, because the OData error seemed to be disagreeing with offense_start_datetime.
Are you suggesting a different way to parse report_datetime other than using the Add Column > Parse in the Power Query editor that would not cause the Power BI Desktop to hang?
Maybe you can describe which reports you want to run off the data. A lot of your transforms do not serve a real purpose (sorting rows or rearranging columns for example). If you cut out all the unnecessary transforms you get the best performance.
Thanks lbendlin , I didn't realize that the sort and column rearranging would bog down Power BI so much when applying changes. I avoided taking those actions and was able to add the column and apply changes without issue.
I'm still unclear why exactly the report refresh ran into the data type error (below), but I'm going to publish this version that's been fully updated with the lastest data and see if hte public report will accept an auto-refresh.
The data provider was unable to convert the value '<pii></pii>' from the source data type 'VT_BSTR' to the expected data type 'VT_DATE'.
Could also have been caused by malformed data. I noticed a couple of empty date times and 0,0 locations.
Since it is an ODATA source you can consider prefiltering if that is appropriate.
The data loaded fine in my tests.