Showing results for 
Search instead for 
Did you mean: 
Helper III
Helper III

Appending issue- Power Query editor

Hello Folks,


While appending the one table into another with same headers and data types, taking too much time as working on SQL via VPN

and at last movement ,it does not complete.

First table having 20 MN records from Jan20 to till date and second having only 3 MN records from Jan20 to till date

I have developed the dashboard on sample data like recent 2 months of records but I want to import all records in PBI destop side then can validate the dashboards and puplish on PBI portal.


Is importing all records in desktop from SQL server correct procedure ? ( doing in Power BI query editor side in command like SELECT * FROM [db_CSA_Data].[Yield_Sec_Test] WHERE invoicedate>='2020-01-01')


Can we change the date range on PBI portal like invoicedate>='2020-01-01'  

If any other way to do this please reply

I am seeking help please anyone could help me on this.






Impactful Individual
Impactful Individual

>Is importing all records in desktop from SQL server correct procedure

When you create a connection to SQL database you have 2 options: Data Import and Direct query. Data Import is a correct procedure in most cases excep really huge databases (doesn't seem to be your case). Data Import makes working with large amounts of data in Power BI very fast because eveything will be stored in memory.

But it's not the best approach to use native SQL queries like "SELECT ..." in Power Query. As already suggested - there is such thing as query folding in Power Query. Connect to the database without using "SELECT ...." query. Keep 'SQL statement' field blank when you're creating a connection to the database. Then select required tables from the list. Then apply all required filters using Power Query commands. Keep only tables/records/columns you will need for the reporting, remove everythig else (using Power Query commands/filters) and make all other required transformations. Read about query folding and it's limitations to make sure query folding is happeing for as many steps as possible. If everything done well (query folding is happening) then Power Query will automatically convert M language code into SQL query and import only required tables/records/columns from the database. If it will be to slow - make sure once again that you loading only really required for the report data, make sure your VPN connection is not a bottle neck, verify (or ask SQL server admin) if there are any performance issues on the server side.

>Can we change the date range on PBI portal like invoicedate>='2020-01-01'  
You can use a parameter for filtering and you can change paramters in Power BI service (so you load only small amount of data in desktop for testing and full amount of data in Service). Anyway, there is a limitation and data loading will fail if can't be complete in a couple of hours in Power BI service. Also, read about Incremental Refresh in Power BI. Remember that you'll need a gateway in Power BI Service if it's on-prem SQL server.


While doing according to above suggestion. getting below error. 

It seems SQL Server Side. Could you please suggest




Thanks Avatorl,

Got it. it is SQL server side error.. will check the DB team

Thanks for reply.

I have removed the query which were written. after that it is working fast like appending, filtering etc.

I will reply once again on this post if get any help or complet successfully .

Thanks again for a such valuable insight.




It is like query folding is not working (based on your description). Read my article here.

if both tables are in th seamen SQL DB, can you get someone to write an append query in native SQL?  That may help (depending on the actual problem)

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors