Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

NAV Loop tables

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?   

4 REPLIES 4
Anonymous
Not applicable

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
Not applicable

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
Not applicable

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
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.