Small and medium business | Business Central, N...

How can i query from PowerBI a 3 level hierarchy for product category item from Business Central

(0) Share

Report

Posted on by KC-25070654-0

Hi all,

I currently have a 3 level hierarchy in my item_category_code field in item master data in Business Central. I would like to pull the whole table of item_category_code to Power BI.

Ex:

- From Business Central: Hygiene -> Soap -> Lemon soap

Any help?

Best,

Kevin

Categories:

Dynamics 365 Business Central

I have the same question (0)

All responses (5)

Answers (0)

Suggested answer

Chris Bulson on at

Like (0)

Report

Hello,

You can use the default Business Central connector in Power BI Desktop to connect to any Web Services that you have published in your Business Central environment. You can publish most pages as a Web Service and access it through Power BI in this method. If you can't access a specific page or set of data in this way you could create an Extension with a Query of the data you need. Then you can publish this Query as a Web Service and consume it in Power BI.

Relevant Links:
https://docs.microsoft.com/en-us/dynamics365/business-central/across-how-use-financials-data-source-powerbi

https://docs.microsoft.com/en-us/dynamics365/business-central/across-how-publish-web-service

If you need a Query to expose a custom data set:
https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/devenv-query-object

Thanks,
Chris

Was this reply helpful? Yes No
matteo4026 50 on at

Like (0)

Report

Hello Chris,

thanks for sharing. My comments:

Before exposing a web service (how to explained in your first link) i have to create a data set in Business central to be exposed via a service. This data sets will be recalled in the column "service name" while creating the web service. I did it and I have no issue here.

210 Resource Units of Measure

5404 Item Units of Measure

My issue is that if i recall the Odata/Soap urls with my browser i display a white page only after authenticating (Odata) and it keeps asking for authentication (Soap).

Any thought?

Was this reply helpful? Yes No
Chris Bulson on at

Like (0)

Report

If you are trying to directly access the Odata endpoint through a browser, then you must user the Username and Password found on your user card in Business Central.
Where username = Username and Password = Web Service key

You cannot use your regular AAD sign-in info.

Thanks,
Chris

Was this reply helpful? Yes No
matteo4026 50 on at

Like (0)

Report

Hello Chris,

now i see some sort of source code but not the content. Is this correct and so to display proper data i need to use power bi?

Regards,

Matteo

Was this reply helpful? Yes No
Chris Bulson on at

Like (0)

Report

Yes, typically you cannot access those endpoints through a browser. You would have to use something like Postman if you wanted to run a GET against it and actually have data returned.

Power BI should also show you results depending on what you setup.

Was this reply helpful? Yes No

Join the conversation

Community site session details

How can i query from PowerBI a 3 level hierarchy for product category item from Business Central

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > Small and medium business | Business Central, NAV, RMS

Featured topics

Product updates

210	Resource Units of Measure
5404	Item Units of Measure

Community site session details

How can i query from PowerBI a 3 level hierarchy for product category item from Business Central

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Small and medium business | Business Central, NAV, RMS

Featured topics

Product updates