Power Query OData with Business Central - Select columns yields errors in unselected columns

(1) Share

Report

Posted on by Jared M

Hello,

I'm using a BC OData URL as a source in a power query in Excel. The source works fine and I can filter the rows in the source itself which speeds up the query. However, I can't get it to select specific columns. When I choose specific columns to bring in, instead of only bringing those in, it brings all columns in and only populates the ones I selected (and fills the other columns with /error/). Is there a way to have the OData exclude these unneeded columns entirely? It doesn't seems to be any faster the way I have it because it still brings in all the columns, it just doesn't populate data for them.

For example, I only want to bring in the Posting Date and the GL Account Number:

Is there a better way to weed out the unneeded columns before they're loaded so the query can update faster?

Thanks,

Jared

I have the same question (1)

All responses (3)

Answers (0)

Judy Microsoft Employee on at

Like (0)

Report

Hi, please specify what the error are you experiencing.

Was this reply helpful? Yes No
Jared M 46 on at

Like (0)

Report

Sorry, the picture didn't load:

It's not a specific error, what's happening is the columns I've not selected are just coming through with errors in every row. If it still doesn't load, it's basically the same as the image in this post: https://forum.enterprisedna.co/t/power-query-odata-connection-with-select/22639

The workaround suggested in that post doesn't help because it weeds out the columns after the data is loaded, so it doesn't help speed up the OData refresh.

I drilled into one of the errors (document type for example) and this is what it said:

DataFormat.Error: We expected a property 'Document_Type', but the OData service omitted it from the response data. This can occur for a number of reasons and does not necessarily imply that the data does not exist or is of a default value.
Details:
Property=Document_Type

But again, the issue isn't the specific error, it's the fact that columns I did not select are still showing up (just filled with errors)

Jared

Was this reply helpful? Yes No
EG-11091351-0 4 on at

Like (0)

Report

Im having the same issue, converting my odata calls to BC, using the same $select method, example ?$select=Job_No and i still get all columns with "Error" in the columns where i didnt specify i wanted the data. If i skipp null and implemention 2.0 settings i get null in every column i didnt want.

Did you figure out a fix for this?

Was this reply helpful? Yes No