Missing Data Error on Refresh

[PowerDR]

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!

[Watsky]

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!

Did my answer(s) help you? Give it a kudos by clicking the Thumbs Up! ?
Did my post answer your question(s)? Mark my post as a solution. This will help others find the solution.

[PowerDR]

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!

[Watsky · Accepted 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.

Did my answer(s) help you? Give it a kudos by clicking the Thumbs Up! ?
Did my post answer your question(s)? Mark my post as a solution. This will help others find the solution.

[PowerDR]

Thanks so much Watsky. I got it sorted by selecting the columns as you said and then added "Remove Other Columns".