Forum Discussion
NAV Loop tables
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.
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?
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.
If you get retrieve a small example on the 1 or 3 way, that would be perfect!