Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all
We have Power BI connected to our Dynamics 365 CRM data source. There have recently been some changes to the tables in Dynamics and now we get a Missing Data Error on Refresh.
I have searched for solutions and came across the MissingField.UseNull option which I think could work for our needs. I have attempted to add it to the query, but do not have success.
See the current query below. Please help me insert the MissingField.UseNull function?
let
Source = OData.Feed("our_data_source", null, [Implementation="2.0"]),
opportunities_table = Source{[Name="opportunities",Signature="table"]}[Data]
in
opportunities_table
Thanks so much in advance!
Solved! Go to Solution.
@PowerDR I don't know of a way around adding all columns but what you can do as a quick way around having to manually add all the column names is to click on Choose Columns select all the columns you need and click Ok. That will add all the column names for you. Then all you have to do is add a comma after the closing bracket } and MissingField.UseNull and close the parenthesis. Similar to the solution I provided above.
Hope this helps.
Proud to be a Super User!
Hey @PowerDR to use MissingField.UseNull you'll need to have more code that lists out the names of the columns you're bringing in. Here's an example:
I have three columns Number, Custom, and Z. Let's say these are the columns that are in your opportunities table. Z doesn't exist anymore but I'm still listing it out in my code.
let
Source = OData.Feed("our_data_source", null, [Implementation="2.0"]),
opportunities_table = Source{[Name="opportunities",Signature="table"]}[Data],
MissFields = Table.SelectColumns(opportunities_table, {"Number", "Custom", "Z"}, MissingField.UseNull)
in
MissFields
The result ends up looking like this.
Hope this helps!
Proud to be a Super User!
Hi Watsky
Thanks so much for the reply. This table is huge with over 400 columns and we need at least 300 of them. Do I need to list them all in the 3rd line of the query or just those I know are missing?
Many thanks!
@PowerDR I don't know of a way around adding all columns but what you can do as a quick way around having to manually add all the column names is to click on Choose Columns select all the columns you need and click Ok. That will add all the column names for you. Then all you have to do is add a comma after the closing bracket } and MissingField.UseNull and close the parenthesis. Similar to the solution I provided above.
Hope this helps.
Proud to be a Super User!
Thanks so much Watsky. I got it sorted by selecting the columns as you said and then added "Remove Other Columns".
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.