Power query load data very slow

[Oscarr]

hi

anyone know how to make power query faster load data out?

i got 20000 plus client, i use power query with between two date(Start Date - End Date), when i choose date and refresh,
that data loading until 20mins only show out, sometime load until non stop.
i use from folder, folder inside got 20 excel file.

Thanks

 

[Chihiro]

You will have to test your set up and identify where the bottle neck is.

I've tested using 37 files, about 26k rows. Takes about 30 sec to refresh query.

Also, you'll have to tell us how you are combining files in a folder. Are you doing it through "Combine Files" ribbon tool in PQ editor? Or are you doing it through some other method?

 

[Oscarr]

You will have to test your set up and identify where the bottle neck is.

I've tested using 37 files, about 26k rows. Takes about 30 sec to refresh query.

Also, you'll have to tell us how you are combining files in a folder. Are you doing it through "Combine Files" ribbon tool in PQ editor? Or are you doing it through some other method?

hi @Chihiro

im using this.

Thanks

 

[Chihiro]

So function is invoked to create column of Table (for each file). Then that's expanded right? That's the standard procedure and shouldn't have issue with merging data.

Check if all files share exact structure (including sheet names) and data types are consistent through out.

 

[Oscarr]

So function is invoked to create column of Table (for each file). Then that's expanded right? That's the standard procedure and shouldn't have issue with merging data.

Check if all files share exact structure (including sheet names) and data types are consistent through out.

Hi @Chihiro

If all data is correct, still that same problem very slow, how to fix it?

Thanks

 

[Chihiro]

What's your computer's spec? CPU, RAM, OS.
And what's your Excel installation bitness?

Other than that, without looking at your file and/or query plan. Hard to say what's causing issue. I've not had issue myself on combining data.

If you can't fix it, workaround is to load the data to SQL database (db) and then query db from PQ.

 

[Oscarr]

What's your computer's spec? CPU, RAM, OS.
And what's your Excel installation bitness?

Other than that, without looking at your file and/or query plan. Hard to say what's causing issue. I've not had issue myself on combining data.

If you can't fix it, workaround is to load the data to SQL database (db) and then query db from PQ.

Hi @Chihiro

my RAM 8GB i7, Excel 64bit..

if i choose date start from 1 jan 2018 to 1 feb 2018 then refresh, that data loading until non stop.
my folder inside got 20 excel files, 600,000k rows..
can you show me how is your's?

SQL database more faster? and which one SQL database? and query db from PQ is what? and how to use?

Thanks

 

[Chihiro]

if i choose date start from 1 jan 2018 to 1 feb 2018

Where and how the dates are filtered? In PQ using date filter function? Or some other method?

I don't usually use query folder, except from OneDrive (for the purpose of giving PowerBI access to data externally).

But here's one of set up that I have.



It's used to transform 3 different table types which share some common fields into one output file for reporting.

 

[Chihiro]

As for SQL, it really doesn't matter which type. As long as it's true database.

Though I'd recommend using MS SQL Express. Since it's free up to 4 or 8 Gb of db size depending on version. It's adequate for the purpose of data storage (non-production server). And since PQ is developed by SQL team at MS. It plays really well with PQ.

Much of aggregation and transformation can be done on SQL server side and need not tax your local machine or Excel.

 

[Oscarr]

Where and how the dates are filtered? In PQ using date filter function? Or some other method?

I don't usually use query folder, except from OneDrive (for the purpose of giving PowerBI access to data externally).

But here's one of set up that I have.
View attachment 50304
View attachment 50305

It's used to transform 3 different table types which share some common fields into one output file for reporting.

Hi @Chihiro

Are you use PowerBi? May I know, if use PowerBi, that data must save in to their server? Can save in our pc and connect with phone?

Thanks

 

[Chihiro]

Yes I use PowerBI as well as Excel PowerQuery & PowerPivot.

Data is not usually saved in the PowerBI server, but rather queried from available location. Usually it should not be from your PC. As it can be turned off, and will cause performance lag depending on number of connection etc.

There are multiple ways to pull data into PowerBI or push data into PowerBI model. Too many to go into detail... but here's few examples.

1. OneDrive - House your Excel files there and query it using Web query
2. GateWay server - For security reasons, many company does not expose their data server externally (except through VPN). So you need to set up gateway server to serve as intermediary to get at the data from public domain.
3. Connect to cloud service - Such as SalesForce, Facebook, Google Analytics etc.

As long as data can be brought into PowerBI service, you can connect to it from your mobile device.

 

[Oscarr]

Yes I use PowerBI as well as Excel PowerQuery & PowerPivot.

Data is not usually saved in the PowerBI server, but rather queried from available location. Usually it should not be from your PC. As it can be turned off, and will cause performance lag depending on number of connection etc.

There are multiple ways to pull data into PowerBI or push data into PowerBI model. Too many to go into detail... but here's few examples.

1. OneDrive - House your Excel files there and query it using Web query
2. GateWay server - For security reasons, many company does not expose their data server externally (except through VPN). So you need to set up gateway server to serve as intermediary to get at the data from public domain.
3. Connect to cloud service - Such as SalesForce, Facebook, Google Analytics etc.

As long as data can be brought into PowerBI service, you can connect to it from your mobile device.

hi @Chihiro

Thanks you for you Detail..
for me a bit Troublesome now..

i'm try another way to fix it, still got problem...how to combining with that value to same name without mercedes? pls check attached file.

Thanks