Skip to main content

Notifications

Announcements

No record found.

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

Excel Power query using API datasource (NOT web services)

(0) ShareShare
ReportReport
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!

Tips for Writing Effective Verified Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,445 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans