Listing all tables from Business Central

If you’ve ever worked with Business Central as your data source, you’ll know that you have 3 different endpoints to connect to. Within each, you’ll find tables of data. Sometimes, the table you’re looking for will be found in V2.0, other times in Odata (Webservices) etc.

When you connect to BC you need to select one of these sources if you’re using the UI in Power Query and then you cannot search for all tables with the name “Sales” regardless of endpoint for example.

This Power Query code will solve that! Run this in a blank query and you’ll list all available tables and see what endpoint they come from in your query! You can also click on the cell with the word “Table” in it to get a preview at the bottom of your screen.

let
    Source = Dynamics365BusinessCentral.ApiContentsWithOptions(null, null, null, null),
    Instance = Source{[Name= <Instance Name> ]}[Data],
    Database = Instance{[Name= <Database Name> ]}[Data],
    Renamed_Columns = Table.RenameColumns(Database,{{"Name", "Endpoint"}, {"DisplayName", "Enpoint DisplayName"}}),
    Keep_Columns = Table.SelectColumns(Renamed_Columns,{"Endpoint", "Data", "Enpoint DisplayName"}),
    Expanded_Data = Table.ExpandTableColumn(Keep_Columns, "Data", {"Name", "Data"}, {"Table Name", "Table Data"}),
    Reorder_Columns = Table.ReorderColumns(Expanded_Data,{"Endpoint", "Enpoint DisplayName", "Table Name", "Table Data"})
in
    Reorder_Columns

I’ve used this myself to save a lot of time when searching for a table that holds the data I’m looking for when creating reports!

Hope it’ll help you out too.

Cheers!