I enjoyed watching this video and recommend others to watch. Host seem to be extremely knowledgeable and expert in Excel He has designed the course I feel extremely good. Worth paying to buy the course. With this sample video I learned a bunch. Thanks for educating the community and appreciate your volunteership. Keep posting some more videos. Thanks a bunch
Thanks for sharin. I wonder if I just want to choose some in 10 file to refresh, how can I do? For example for 10 months of data, i just want to fix 7 months and update 3 months. How can I do to save time when refreshing
I like comparisons like that too, helps me with the why. But don't forget, small data sets, Connection Only is quite nice so we only have one refresh. You are welcome as always!
Yup... That was such a real magic. :) Can't thank you enough or appreciate enough for your work Mike. My Excel journey was started with your tutorials.. :D :)
I am getting on with hard core PQ, Power Pivot activities. The relevant Magic Tricks are great help. (and the entire series on data analysis, of course )Thank you. Am I correct to assume that, with every new version, Excel has intentionally moved towards database management - SQL and the like - to become a greater tool for data management, and go beyond conventional spreadsheet services. (with Lambda, Excel has also reached out to programming - again, unconventional for a simple spreadsheet app.)
I hope you are using this playlist: th-cam.com/play/PLrRPvpgDmw0ks5W7U5NmDCU2ydSnNZA_1.html It has the full MSPTDA Data Analysis class, plus many other Magic Tricks for DAX and Power Query. Microsoft has moved Power Query towards SQL, in that when you connect to an SQL database, Power Query builds SQL that it sends back to the SQL database for more efficient execution. But I don't think Excel or Power BI is "database management", both tools are built to perform analytics, rather than database management. You are right about LET and LAMBDA in that they move Excel closer to a programming language : )
@@excelisfun Yes, you are right, analytics is the better term, not DB management. Yes, I use MSPTDA and the E DAB videos. Both of them are saved in my Playlist, together with some single videos from the Dragon series, Magic Tricks etc. Thank you.
Hi Mike, I a big fan of you...you are absolutely great in explaining this stuff so everyone (even me) is able to understand it. 🙂 Nevertheless, is there no way to set a parameter in the first step already to import certain columns only BEFORE loading the data into the power query editor itself? I know for CSV files at least you can select certain number of columns in the Csv.Document command, but is there a similar option to do with Excel Files with Excel.Workbook command which you load from a folder? I was searching for it since a long time but w/o success at all yet. My issue is that I want to keep this Master Excel file as small as possible as data from the Excel file in the folder is growing everyday I am reading from. Apart from that refresh takes too long time if I first load all columns and then remove columns I dont need in second step. For instance, I can do that in KNIME very easily but my management does not know KNIME at all... 🙂 Thank you for you feedback. Regards, Janni
Loved it! So if we get new data in the original file source and your Pivot Table is based off the Data Model then you just refresh the Pivot Table to get the updated data? Also, what's the Fast Data Load option in Power Query? Will that increase speed for the Connection Only method? Thanks!
It's a click box near the bottom of certain Query Properties dialog boxes. It's called Fast Load Data (It says by enabling this your data will load faster but Excel might become unresponsive for long periods of time).
Hi Mike. Thanks so much for the content you put out there - your efforts have helped me big time in my work and I have developed a love for Excel (I knew zilch before watching your videos). I do have a question though. I used the Data Model method as illustrated in the video, but my file size is just over 100MB. I have 132 columns and 1.1M rows. The Pivot Table is working fine with minimal lag, but for my understanding, I am wondering why my file size is so large (can't seem to get an answer with standard googling). Thanks in advance!
Hello, i'd like to ask, you example here has about 7mil lines. My data is only 3mil, but i could not do the pivot table. I will always get this alert "Microsoft Excel cannot make this change because there are too many row or column items..............". May you help me to address this?
Hi! I'm very excited to start using Power Pivot! I have some very large files that I would like to like to add to a Data Model via a connect because I am building some dashboards. I want to get the data from a folder, so all I have to do is dump the files in the folder and refresh my dashboard workbook. However, I have one worksheet with a dashboard for Active Employees and another worksheet within the same workbook for Termed Employees. I cannot have these datasets merged because they are different. I can't seem to create 2 queries from one folder.
Cool. I'm using Excel 2010 with PQ as an add on. Click Power Query tab > Options > Data Load > Fast Data Load (you can check/uncheck it). You can also view this option by hovering over one of your Connections in your Power Query Workbook Queries > select the ellipses (......) > Properties > Fast Data Load
Superb. Many thanks for sharing such kind of knowledge. Major difference between data model and connection only, one is faster and compress file size and other dont.. Please correct if i m wrong.....
You are correct. Yes, I have done a number of videos on this topic. Connection Only can be slower sometimes in refreshing, but file size should be smaller.
Great material, thanks! :) The only difference that I have got is on the importing phase: while selected proper folder and clicking on the two arrows to see the fields it opens a view per each file and then when I have connection it is not a one query, but actually a set of five queries... Could you please comment what I am doing wrong?
I have a question, let's suppose you came back a month later and now have an additional text file, you drop it in the folder, do you just refresh the query or do you have to recreate from scratch?
Hello, I have a table with 6 colums but with 21420 rows , couldnt past it into Excel, do you have a method for doing that? I have MS Excel 2007. Thanks ..
Hii, I downloaded my data into txt. Version. But when I import my data, some of the column runs causing it to hit error that show unable to convert to number. I get to append a few data together but I did not get the same error as my big files. Is there any solution for ignoring the error, and let my data appear?
when i change the data type, some of my dates of data show error, although before changing data type i can see as a date. why is showing this don't get it. Could you please advise.
For anyone having errors with this problem, there is an error in the "header" (first-) row of the text files 2017-01 and 2017-03. This begins: Date Time{tab space}Web Site{tab space}... In all the other files this is: Date{tab space}Web Site{tab space}... Replace "Date Time" with "Date". Excel/Power Query should now work fine, if a little slowly, with all these records.
Thank you for tracking down this error. I have changed the two files :2017-01 and 2017-03. For each I changed "Date Time" to "Date". I hope this fixes the problem. Thanks, ContendSoul for being a great Online Excel Teammate and helping me out : )
hey, good Explanation, but it didn't work for me... i use several excel files that located in 2-3 folders. The total files size are almost 200MB. After importing and manipulating them a little bit, i loaded the final query to the Data Model and saved it. At the end of the process the file size (with the model) was 70MB which is very big. The dimentions are 600K rows X 26 Cols. Any idea what went wrong in process ?
Mot sure, but maybe: Since the Data Model in-memory Columnar Database only stores unique values, if your data set had many different values (not-unique or distinct), maybe file size was not reduced as much.
I have tried so many times, the file with data model + Pivot is always much bigger than connection only + Pivot. Who knows why? Data are extracted from XLXS files. There are about 700 thousand rows and 30 columns, when data are loaded pure through Power Query to Data Model, the file size more 27M.
My work's database management system is exporting data tables just fine but the first 9 rows contain information that disrupts power query because the first 9 rows have a small amount of information data that take up only two the first two columns. Power query then creates its table and thinks I only have 2 columns of data and doesn't display the remaining 20 columns that start in row 10. Is there a way to fix this in power query?
It sounds like a specific issue with this database. I have not encountered this issue before. The key with Power Query (and all data cleaning, querying and importing) is to recognize a consistent pattern in the data that can be used to get the output you want. Maybe, if the consistent pattern is always that the top 9 rows are faulty, then remove top 9 rows with Remove Top Rows feature. This feature is in Home Ribbon Tab, Reduce Rows group, Remove Rows drop-down, then Remove Top Rows.
Hi I am not able to reduce the size of the file. I tried to import a text file using data query before making a Pivot table report. I have posted the query in below forum: chandoo.org/forum/threads/excel-power-query-get-and-transform-import-big-data-text-files-connection-only-or-data-model.35668/
It’s magical!
Wow, I didn’t know this was even possible. Now, this approach has unlocked so many doors!
Thanks Mike!
You are welcome for the magic!!!
I enjoyed watching this video and recommend others to watch.
Host seem to be extremely knowledgeable and expert in Excel
He has designed the course I feel extremely good. Worth paying to buy the course.
With this sample video I learned a bunch.
Thanks for educating the community and appreciate your volunteership.
Keep posting some more videos.
Thanks a bunch
You are welcome!
I agree with you except the word "seem". WITHOUT any doubt, Mike IS extremely knowledgeable. :)
My plant manager is going to be so happy our OEE data will take seconds to update and less time to pivot! Awesomee, thank you
Thanks ExcelIsFun. I had a 167 MB txt file I was having issues with even loading. This helped a TON!
You are welcome!
Thanks ExcelIsFun.
You saved me so much googling! Finally someone explaining these functions in a useful and easy to understand way. Thank you!
You are welcome, Matt!! Thanks for your support : )
What database is the data imported into when using data model?
Thanks a lot for Excel is fun channel! 👍
Thanks Mike! Love to watch all your videos, always learning so much about Excel and Power Query!
Glad you can learn so much : )
This is so helpful for me! Thank you!
You are welcome, Monica!
Clear explanation for the big data problem. Appreciate your existence...
Glad that the existence of the videos helps!
Thanks for sharin. I wonder if I just want to choose some in 10 file to refresh, how can I do?
For example for 10 months of data, i just want to fix 7 months and update 3 months. How can I do to save time when refreshing
Super clear and exactly what I wanted to know. Thanks so much!
You are welcome so much!!!!
Data Model is a role model
Wow, that's a big size difference. I like comparisons like that. Thanks.
I like comparisons like that too, helps me with the why. But don't forget, small data sets, Connection Only is quite nice so we only have one refresh. You are welcome as always!
Yup... That was such a real magic. :) Can't thank you enough or appreciate enough for your work Mike. My Excel journey was started with your tutorials.. :D :)
I am getting on with hard core PQ, Power Pivot activities. The relevant Magic Tricks are great help. (and the entire series on data analysis, of course )Thank you. Am I correct to assume that, with every new version, Excel has intentionally moved towards database management - SQL and the like - to become a greater tool for data management, and go beyond conventional spreadsheet services. (with Lambda, Excel has also reached out to programming - again, unconventional for a simple spreadsheet app.)
I hope you are using this playlist: th-cam.com/play/PLrRPvpgDmw0ks5W7U5NmDCU2ydSnNZA_1.html
It has the full MSPTDA Data Analysis class, plus many other Magic Tricks for DAX and Power Query.
Microsoft has moved Power Query towards SQL, in that when you connect to an SQL database, Power Query builds SQL that it sends back to the SQL database for more efficient execution. But I don't think Excel or Power BI is "database management", both tools are built to perform analytics, rather than database management. You are right about LET and LAMBDA in that they move Excel closer to a programming language : )
@@excelisfun Yes, you are right, analytics is the better term, not DB management. Yes, I use MSPTDA and the E DAB videos. Both of them are saved in my Playlist, together with some single videos from the Dragon series, Magic Tricks etc. Thank you.
@@zt.5677 You are welcome, Z T!!!!!
Hi Mike, I a big fan of you...you are absolutely great in explaining this stuff so everyone (even me) is able to understand it. 🙂
Nevertheless, is there no way to set a parameter in the first step already to import certain columns only BEFORE loading the data into the power query editor itself?
I know for CSV files at least you can select certain number of columns in the Csv.Document command, but is there a similar option to do with Excel Files with Excel.Workbook command which you load from a folder?
I was searching for it since a long time but w/o success at all yet. My issue is that I want to keep this Master Excel file as small as possible as data from the Excel file in the folder is growing everyday I am reading from. Apart from that refresh takes too long time if I first load all columns and then remove columns I dont need in second step.
For instance, I can do that in KNIME very easily but my management does not know KNIME at all... 🙂
Thank you for you feedback.
Regards, Janni
Loved it! So if we get new data in the original file source and your Pivot Table is based off the Data Model then you just refresh the Pivot Table to get the updated data? Also, what's the Fast Data Load option in Power Query? Will that increase speed for the Connection Only method? Thanks!
Yes, Just one refresh with Data Model. I don't know what "Fast Data Load option" is, where did you see it?
It's a click box near the bottom of certain Query Properties dialog boxes. It's called Fast Load Data (It says by enabling this your data will load faster but Excel might become unresponsive for long periods of time).
Hi Mike. Thanks so much for the content you put out there - your efforts have helped me big time in my work and I have developed a love for Excel (I knew zilch before watching your videos). I do have a question though. I used the Data Model method as illustrated in the video, but my file size is just over 100MB. I have 132 columns and 1.1M rows. The Pivot Table is working fine with minimal lag, but for my understanding, I am wondering why my file size is so large (can't seem to get an answer with standard googling). Thanks in advance!
Hello, i'd like to ask, you example here has about 7mil lines. My data is only 3mil, but i could not do the pivot table. I will always get this alert "Microsoft Excel cannot make this change because there are too many row or column items..............". May you help me to address this?
Hi! I'm very excited to start using Power Pivot! I have some very large files that I would like to like to add to a Data Model via a connect because I am building some dashboards. I want to get the data from a folder, so all I have to do is dump the files in the folder and refresh my dashboard workbook. However, I have one worksheet with a dashboard for Active Employees and another worksheet within the same workbook for Termed Employees. I cannot have these datasets merged because they are different. I can't seem to create 2 queries from one folder.
Cool. I'm using Excel 2010 with PQ as an add on. Click Power Query tab > Options > Data Load > Fast Data Load (you can check/uncheck it). You can also view this option by hovering over one of your Connections in your Power Query Workbook Queries > select the ellipses (......) > Properties > Fast Data Load
Got it! Thanks for the hot tip! : )
Good Example in difference in file size.
Glad it is helpful! : )
Superb. Many thanks for sharing such kind of knowledge. Major difference between data model and connection only, one is faster and compress file size and other dont.. Please correct if i m wrong.....
You are correct. Yes, I have done a number of videos on this topic. Connection Only can be slower sometimes in refreshing, but file size should be smaller.
Great material, thanks! :)
The only difference that I have got is on the importing phase: while selected proper folder and clicking on the two arrows to see the fields it opens a view per each file and then when I have connection it is not a one query, but actually a set of five queries... Could you please comment what I am doing wrong?
I actually have the same problem when in every video I saw, everyone has only one query..
Many Thanks for your help
has power query learnt to use a.docx file yet without having to convert it to text?
Excellent! Thank you!
I cannot choose only create connection, please help]
I have a question, let's suppose you came back a month later and now have an additional text file, you drop it in the folder, do you just refresh the query or do you have to recreate from scratch?
just refresh
Fauststein thank you!
Amazing work!
Glad it helps!
Hello, I have a table with 6 colums but with 21420 rows , couldnt past it into Excel, do you have a method for doing that? I have MS Excel 2007.
Thanks ..
Thanks for this! Very helpful
Awesome Video - helped me save so much time - thank you
Instead of Sum of values kindly show the COUNT so that one can realise the number of rows.
I learn something new everyday with your help.Thanks Mike :-)
Glad the videos help and you learn something each day, K B : )
Great comparison! Thanks Mike!
Glad yoy like it, Awesome Online Excel Teammate!
Hi Mike ! Great video. Looking forward to see more PQ-Stuff... Thumbs up !!
Lots more is coming! Thanks for the Thumbs Up and Sub! : )
How to import data in power query if empty cells have between cell
Hii, I downloaded my data into txt. Version. But when I import my data, some of the column runs causing it to hit error that show unable to convert to number. I get to append a few data together but I did not get the same error as my big files. Is there any solution for ignoring the error, and let my data appear?
Power BI Error - Correcting the error: We couldn't parse the input provided as a Date value. - th-cam.com/video/iD7gNBcYjEE/w-d-xo.html
when i change the data type, some of my dates of data show error, although before changing data type i can see as a date. why is showing this don't get it. Could you please advise.
Awesome, great video
Stunning!
Great video. Thank you Mike
BTW, "add this data to the Data Model" feature is not in Excel 2010 even if you download the PQ add on.
Yes, that is correct. It was added in Excel 2013.
This is great, thank you!
You are welcome!
For anyone having errors with this problem, there is an error in the "header" (first-) row of the text files 2017-01 and 2017-03.
This begins:
Date Time{tab space}Web Site{tab space}...
In all the other files this is:
Date{tab space}Web Site{tab space}...
Replace "Date Time" with "Date".
Excel/Power Query should now work fine, if a little slowly, with all these records.
Thank you for tracking down this error. I have changed the two files :2017-01 and 2017-03. For each I changed "Date Time" to "Date". I hope this fixes the problem.
Thanks, ContendSoul for being a great Online Excel Teammate and helping me out : )
hey,
good Explanation, but it didn't work for me...
i use several excel files that located in 2-3 folders. The total files size are almost 200MB. After importing and manipulating them a little bit, i loaded the final query to the Data Model and saved it.
At the end of the process the file size (with the model) was 70MB which is very big. The dimentions are 600K rows X 26 Cols.
Any idea what went wrong in process ?
Mot sure, but maybe: Since the Data Model in-memory Columnar Database only stores unique values, if your data set had many different values (not-unique or distinct), maybe file size was not reduced as much.
Worked like a magic !!!
Thank you !
I have fixed the source data sets, so I hope after you download the text files again and re-try, everything will work.
Thank you Mike for this great video.
Nice presentation.
Glad you like it!
I have tried so many times, the file with data model + Pivot is always much bigger than connection only + Pivot. Who knows why? Data are extracted from XLXS files. There are about 700 thousand rows and 30 columns, when data are loaded pure through Power Query to Data Model, the file size more 27M.
Becasue Connection Only does not load all the raw data, all other methods do : )
My work's database management system is exporting data tables just fine but the first 9 rows contain information that disrupts power query because the first 9 rows have a small amount of information data that take up only two the first two columns. Power query then creates its table and thinks I only have 2 columns of data and doesn't display the remaining 20 columns that start in row 10. Is there a way to fix this in power query?
It sounds like a specific issue with this database. I have not encountered this issue before. The key with Power Query (and all data cleaning, querying and importing) is to recognize a consistent pattern in the data that can be used to get the output you want. Maybe, if the consistent pattern is always that the top 9 rows are faulty, then remove top 9 rows with Remove Top Rows feature. This feature is in Home Ribbon Tab, Reduce Rows group, Remove Rows drop-down, then Remove Top Rows.
Thanks man!! I've learned a lot from you.
Awesome! Glad it all helps! Thanks for the continued support : ) Tell all your friends too : )
Yes.
I agree : )
Its easy to understand, thanks :) Subscribed
Thank you for your Sub : )
Not working fine for me, text files returns null columns in power query .. I don't know why..
I have fixed the source data sets, so I hope after you download the text files again and re-try, everything will work.
thank you
You are welcome!
that was interesting, thanks
You are welcome, Lesia!!!
Cool!! :)
: )
Very helpful, thanks Mike.
Hi I am not able to reduce the size of the file. I tried to import a text file using data query before making a Pivot table report. I have posted the query in below forum: chandoo.org/forum/threads/excel-power-query-get-and-transform-import-big-data-text-files-connection-only-or-data-model.35668/