• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel Power Query: Get and Transform : Import Big Data Text Files: Connection Only or Data Model?

K

Kumar Shanmugam

Member
Hi

I wanted to create a dashboard file which will be linked to big excel data ( more than 7 lakh records). I tried to do as per the video tutorial "Excel Magic Trick 1336: Power Query: Import Big Data Text Files: Connection Only or Data Model?"

Link: https://youtu.be/HYEOvkLSY-0

I have linked the dashboard file data to big excel data through connection only and data model. but my file size is not reduced. Its still the size of big excel data. I don't know where I am wrong.

Also I wanted to share you the base data but file size is more than 1 MB. Please refer the attached screenshot.

upload_2017-9-1_9-46-57.png
 
I don't have powerquery addin installed. I am doing it through Data>New Query

I found one option under Data>New Query>Query Options> Global/Data load. I have unchecked the same but still not working.

upload_2017-9-1_10-8-20.png
 
Get & Transform is same thing as PowerQuery. Just named differently for Excel 2016.

At any rate, loading to model will reduce size depending on source data structure, by about 50~80%. But the main advantage of PowerQuery is in ease of data transformation and merging various source into single model.
 
Hi my source data is 35 MB and dashboard data which I have created is also 35 MB. It does not have any effect even though I have linked the dashboard file data to big excel data through connection only and data model.

upload_2017-9-1_17-4-34.png
 
Can't really say without seeing what's in the data and how you are bringing it in.

But for an example see below. I combine 6 different source text files in data model in Consolidated.xlsb file.

upload_2017-9-1_7-38-51.png

Now, if you are doing analysis on the data. Why not drop off unnecessary data or do aggregation in the model to reduce size?
 
Hi, Please find attached the source data file (sample file) which is in CSV format.

I am using following method to import the file through Data Query> Connection only and load to data model.

Data>New Query>From File> From CSV> Select the Source file.

Load to>

upload_2017-9-2_10-0-36.png
 

Attachments

  • Rawdata02.zip
    230.9 KB · Views: 6
  • Dashboardfile02.zip
    746.1 KB · Views: 6
There's something odd with your CSV. Not sure what it is. I'll have to do more digging.

It's fine when connection is made. But as soon as data is loaded to model, it bloats file size by about 5 times original csv's size.

Which is odd, as I usually see opposite (data model file is about 1/5 to 1/8 of original csv/text file).

Try reading through the link and see if you can optimize the model.
https://support.office.com/en-us/ar...t-add-in-951c73a9-21c4-46ab-9f5e-14a2833b6a70
 
Hi Can you share your sample base date which you have tried to compress to less MB. I wanted to check whether there is any problem with the query setting.
 
You must log in or register to reply here.
Back
Top