Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
PaulGBG
Frequent Visitor

OLE DB ODBC Error connecting to Microsoft Business Central

Hi.

I need to filter a query on a field that is held in the customer card table in Microsoft Business Central.

I have managed to get this table back from other companies where the number of customers is far less than this specific table with no problems, but cannot bring records back into power BI desktop from this company.

 

I have set up a new Power BI desktop report, and just tried to bring back the minimum two fields from this table. The Customer Number and the Customer Posting Group. So this is all that I am trying to import to Power BI.

 

I selected these two fields using:

Power Query Editor>Choose Columns

This is a big table, more than 3000 rows.

 

Here is the query from advanced editor:

 

let

    Source = Dynamics365BusinessCentral.EnvironmentContents(null, null),

    PRODUCTION = Source{[Name="PRODUCTION"]}[Data],

    #"GBG LIVE" = PRODUCTION{[Name="GBG LIVE"]}[Data],

    Customer_Card_table = #"GBG LIVE"{[Name="Customer_Card",Signature="table"]}[Data],

    #"Removed Other Columns" = Table.SelectColumns(Customer_Card_table,{"No", "Customer_Posting_Group"})

in

    #"Removed Other Columns"

 

This doesn't work. I get the message below.

 

PaulGBG_0-1616713200132.png

Does anyone have any idea how I can get this data into Power BI?

1 ACCEPTED SOLUTION
PaulGBG
Frequent Visitor

Hi.

 

I want to post the solution to this, as it may save other people a lot of time.

 

The table was just too big and timed out on loading.

 

Our Business Central support provider was able to create a webservice for a filtered page, showing only the two columns that I required.

This page loaded in less than a two minutes with all of the required data for my report.

View solution in original post

3 REPLIES 3
PaulGBG
Frequent Visitor

Hi.

 

I want to post the solution to this, as it may save other people a lot of time.

 

The table was just too big and timed out on loading.

 

Our Business Central support provider was able to create a webservice for a filtered page, showing only the two columns that I required.

This page loaded in less than a two minutes with all of the required data for my report.

amitchandak
Super User
Super User

@PaulGBG , This usually when power bi not able to connect with source. Close without saving. Under edit connection, clear permission and provide credentials and try again your changes. 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Hi Amit.

 

Doing this still doesn't let me bring in the table from Business Central.

 

I can bring through other tables, but not this one.

 

I have access to the table from the business central user interface, but when I try and bring this in, the message stops on.

 

"Waiting for Dynamics365 Business Central"

 

It sticks on this message for about twenty minutes, and then goes to the OLE ODBC error message as above.

 

Do you have any other ideas?

 

Thanks Paul

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.