Appending issue- Power Query editor

[Anonymous]

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.

Thanks

Regards

Shiv

[avatorl]

>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.

[Anonymous]

Hi,

While doing according to above suggestion. getting below error. 

It seems SQL Server Side. Could you please suggest

[avatorl]

https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-802-database-eng...

[Anonymous]

Thanks Avatorl,

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

[Anonymous]

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.

Regards

Shiv

[MattAllington]

It is like query folding is not working (based on your description). Read my article here. https://exceleratorbi.com.au/how-query-folding-works/

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)