Append Tables from SQL Query

[salmanmohammad]

Hello all,

I have a lot of tables on the SQL server with the same structure and am trying to create a union of all of them for use in data visualization in Power BI. What I mean is I am trying to have the data for all the tables one below the other since the table structures for all is the same. I tried using the following SQL query to perform this action:

SELECT * FROM ABC.UVW

UNION

SELECT * FROM DEF.XYZ

And so on…

Now, this operation does create a new table “ALL DATA” with a union of data from all the required tables, but loading this in Power BI and then using it for visualization is a problem because it is just too much data and it takes a lot of time to load/create charts. I am trying to understand if there is a way that helps me do the following:

1. The user gets to select the names of a few data tables from a filter (very similar to data source filter in Tableau) so that the loading time is reduced.
2. This manipulates the existing data table “ALL DATA” in Power BI so that instead of loading all the tables from SQL server, it just keeps the ones selected in the above filter and removes all others.
3. Updates the visuals with the information/aggregates based on the above filters.

Thanks in advance!

[v-yuta-msft]

Hi salmanmohammad,

Query Parameter may help you, see similar case below:

https://community.powerbi.com/t5/Desktop/How-to-change-data-source-using-script/m-p/353574#M159900.

Regards,

Jimmy Tao

[salmanmohammad]

Hi Jimmy,

Thank you for responding. 

I created a parameter and in Suggested Values, I picked "List of Values". Should the inputs for List of Values be Table Names from SQL or the name of the SQL Server? I tried this with Table Names and hit on OK, then 'enabled load' but it gives me an error - "Combining DirectQuery and imported data isn't supported". Please help!

Also, I think this solution helps me pick a database, but not tables. I have a single SQL server and database, but multiple data tables under the database and I want to be able to pick selectively the data tables to load. Not sure, please help!

[v-yuta-msft]

Hi salmanmohammad,

If you are using direct query, please don't enable the parameter. Then it can work.

Regards,

Jimmy Tao