Recipe: Excel and Power BI as OData clients
This recipe is built on LaravelUi5 Core. Core gives every app its own OData service, with its own URL and its own answer to whether a login is needed, so there is nothing to route or mount. Core is free to use; installing it needs a free account.
Excel (through Power Query) and Power BI Desktop read OData v4 feeds without an add-in. The service behind a Core app is such a feed. The one from the UI5 table recipe is enough to follow along.
1. The feed URL
The feed is the app's OData service root. For the Showcase app:
https://your-host/odata/com/example/[email protected]/The namespace is written with slashes, not dots. The sets on offer are exactly the ones configure() binds: one discoverModel() per model you want in the spreadsheet.
The Showcase app is built without authentication, so Excel and Power BI connect anonymously.
Anonymous means public
Whatever the app binds in configure() can be read by anyone who knows the URL. Serve a feed like this only for data you would publish anyway, or behind a network boundary you control. The engine is read-only by design, so a feed can be read and never written.
2. Excel
In Excel for Windows (Microsoft 365, or Excel 2019 and later):
- Data → Get Data → From Other Sources → From OData Feed.
- Paste the feed URL and confirm.
- When Excel asks how to connect, choose Anonymous.
- The Navigator lists the entity sets from
$metadata. Tick Select multiple items to take several at once; relationships between them come along. - Load puts each set into a worksheet as a table. Refresh on the Data tab reloads it.
Excel types the columns from the schema, so dates arrive as dates and numbers as numbers.
3. Power BI
In Power BI Desktop:
- Get data → OData feed.
- Paste the feed URL, choose Anonymous, and connect.
- In the Navigator, select the sets you want and Load.
Power BI reads the relationships from $metadata as well, so a report can join the sets straight away.
4. What the client sends
Both clients read $metadata first, then request each set. A set larger than the page size (200 rows unless you configure it) arrives in pages: the client follows the @odata.nextLink in each response until there is none. Steps you add in the Power Query editor, such as removing columns or filtering rows, are sent to the server as $select and $filter where possible.
Until the next laravelui5/odata release
The next link of a paged response currently keeps only $skip and drops the other query options (Pagination). For a set above the page size, filter and pick columns in the worksheet or report after loading, not in the Power Query editor. Sets below the page size are not affected.
Where to go next
- Which Eloquent casts become which column types: model discovery.
- A set that is not a single model, such as a SQL view for reporting: custom entity sets.
- The same service in a UI5 table: the UI5 table recipe.