Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics 365 | Integration, Dataverse... / Power Query OData with...
Microsoft Dynamics 365 | Integration, Dataverse...
Unanswered

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

(1) ShareShare
ReportReport
Posted on by Jared M 40
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
  • EG-11091351-0 Profile Picture
    EG-11091351-0 4 on at
    Power Query OData with Business Central - Select columns yields errors in unselected columns
    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?
  • Jared M Profile Picture
    Jared M 40 on at
    Power Query OData with Business Central - Select columns yields errors in unselected columns
    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
  • Judy Profile Picture
    Judy Microsoft Employee on at
    Power Query OData with Business Central - Select columns yields errors in unselected columns
    Hi, please specify what the error are you experiencing.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,235 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Get batch job history log from D365 FO to Power Automate

Product updates

Dynamics 365 release plans