NAV Loop tables

[Anonymous]

Hi PBI Gurus!

Is it somehow possible to loop over tables in Power BI by connecting to a NAV Database through a SQL Server?

If you look at a G/L Entry table in a NAV databse, then a NAV database can consist of many companies and each G/L Entry table is placed in each entity. 

I can easily extract the same table for each entity, but is it possible to automatize this process with a SQL script, so it dynamically loops through the companies - like a Union all function?   

[Anonymous]

You need a table with all the company names. In NAV that is Company. I think you can loop through this table in Power Query, compose the table names like [COMPANYNAME$G_L Entry] and merge all in a single table.

But you need to add a new column to this table with the company name.

When I connect to NAV with several companies, for me it is much easier to create a Data Warehouse and let Integration Services do the dirty job.

[Anonymous]

So I need to manually extract the tables (lets say there is 10 companies = G/L Entry tables), and then create a company column for each table and finally append the tables into one.

Is there a more dynamic way of setting this up, in a script instead of manually finding all the tables and append them, as you ref. to when extracting the Company table, which consists of all the companies in the database?  

[Anonymous]

Yes that's right.

I have done it before, but I'd have to search to give you and exact answer. I don't have the example here right now.

You have several ways to do it:

- You can use a stored procedure and a cursor to get al the names in the Company table, get the real table name, take only the columns you need, add the Company Name column, and append the rows into one single table.

- Integration services.

- Or you can create a function in Power Query that does the same.

[Anonymous]

If you get retrieve a small example on the 1 or 3 way, that would be perfect!