cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
salmanmohammad Frequent Visitor
Frequent Visitor

Append Tables from SQL Query

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!

3 REPLIES 3
Community Support Team
Community Support Team

Re: Append Tables from SQL Query

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 Frequent Visitor
Frequent Visitor

Re: Append Tables from SQL Query

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!

Community Support Team
Community Support Team

Re: Append Tables from SQL Query

Hi salmanmohammad,

 

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

 

Regards,

Jimmy Tao

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 307 members 3,179 guests
Please welcome our newest community members: