Dynamic SQL table import

excelmjr
Posts: 6
Joined: Thu Dec 17, 2015 2:43 am

Dynamic SQL table import

Postby excelmjr » Mon Jul 03, 2017 12:49 pm

I am querying our Dyanmics AX 2012 R3/Solver Bi360 SQL server to get information about what is in all of the tables. I have written a Power Query that uses SQL.Databases("server","database") to return a list of all the databases on the server. I have cleaned up the data by leaving only tables and views. Then I added a Table.RowCount([Data]) to show me the number of rows in each table. I filtered to remove any table with zero rows. (This I hope will make it more dynamic in the future.)

So that leaves me with a list that has "Name", "Data", "Schema", "Item", "Kind" and "RowCount". Name and Item are the same information. Data is the green Table or view with the expansion arrows on the row header.

That query is saved as a connection. I am trying to reference that query to then have it import data from all of those tables/views listed into individuals tables in Power BI/Excel 2016 both are 64 bit versions. [I should have put a question mark here].

This then will allow for me to start to investigate the contents of each table and begin to make sense of it.

Thanks for your help. I am happy to supply more information.
Last edited by excelmjr on Mon Jul 03, 2017 8:56 pm, edited 1 time in total.

MattAllington
Posts: 887
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Dynamic SQL table import

Postby MattAllington » Mon Jul 03, 2017 12:53 pm

so what is the question/problem?
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax

excelmjr
Posts: 6
Joined: Thu Dec 17, 2015 2:43 am

Re: Dynamic SQL table import

Postby excelmjr » Mon Jul 03, 2017 8:55 pm

Thanks Matt.

Now that I have a list of those tables that contain data. How do I get from that point to having the tables import into Power BI as individual tables?

MattAllington
Posts: 887
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Dynamic SQL table import

Postby MattAllington » Tue Jul 04, 2017 4:22 pm

The first thing I would do is go to your last query, right click on the last step in the query pane to the right, then check if Query Folding is occurring (view native query). You are going to want native queries as this is the most efficient way to load the data. Native queries only work under certain circumstances. If this feature is greyed out, I would consider deleting steps until Native Queries are available again, then come up with an alternate plan to do what you want.

assuming all OK above, create a new query referencing the connection. Then click on one of the table/links to expand/import the data
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax


Return to “Power Query”

Who is online

Users browsing this forum: No registered users and 1 guest