Skip to main content

Notifications

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

Excel Power query using API datasource (NOT web services)

Posted on by 95

Hi All,

I have for a number of years successfully used web services to publish a page and connect to the data in excel using power query.

With Power BI now using v2.0 API's there are a number of tables already published through what is intended to be a more robust medium.

Does anybody know best practice for connecting to these API's using power query in excel rather than Power Bi?

I can connect to a header table e.g. Sales order header or a single line record e.g. item but cannot extract a lines table.

Unlike in Power Bi, Excel query does not bring in the related child table as an expandable column.

I've searched quite a lot online and have only found my own query on the same topic from last year.

Thanks in advance.

  • Guy-JM Profile Picture
    Guy-JM 95 on at
    RE: Excel Power query using API datasource (NOT web services)

    Thanks Steven, I have only just got around to testing this and can confirm it does work.

  • Verified answer
    Steven Renders Profile Picture
    Steven Renders 5,051 Super User 2024 Season 1 on at
    RE: Excel Power query using API datasource (NOT web services)

    I would suggest to use Power BI Dataflows:

    Areopa webinars: Why are dataflows so interesting for Power BI and Business Central? – think about IT

    Power BI Dataflows now support Business Central! – think about IT

    How Do I: Extract data from Business Central? – think about IT

    The dataflows you can then access in Excel, Power BI Desktop,...

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!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,188 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans