Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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.

Thanks

Regards

Shiv

 

 

6 Replies

  • avatorl's avatar
    avatorl
    Impactful Individual
    5 years ago

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