This is one of the most valuable Excel videos I've seen on GTQ. So thank you, Mark. One snag I ran into as a USA user was the date format, which I think other users may run into. Using the Change type > Using Locale… > Date > English USA will not work if the UK date begins on a day (the first number in the format) above 12. (I'm using Excel 2016 so I know nothing about what newer versions may do in this case.) My solution was simply to split the columns by delimiter, place the 3 new columns in the order I need them, then merge them as a new date column. Now everything is copasetic.
Thanks for that feedback, I really appreciate it. 😁 The locale setting is not the target format, but the source data format. So, if the dates are a UK format you would use English (United Kingdom). Your region settings already know you are based in the US. So selecting English (United Kingdom) would convert from UK to US.
Very powerful tutorial with easy to understand " basic concept". Thanks for such a superb material and content. Sir may I request you to upload a tutorial explaining multiple headers in PQE. Thanks.
Awesome tutorial Mark! I have been using method 2 with xlsx files since then. However I face a performance issue: I have raw data (about 50-100MB) with hundred thousands of rows. Every transformation take ages. It takes sometimes hours. Every time it goes through all rows. I wonder if it is possible to load the file into the RAM first before I start the transformation. So the loading would go much faster. Can you advise how to handle huge data?
The Mac version on Excel is a long way behind Windows. With A Power BI licence you get access to Data Flows, which is Power Query in the cloud. These can be refreshed through Excel Online. So that is an option. Or get really good at VBA or Python.
I've already covered that here: - exceloffthegrid.com/rename-columns-in-power-query-when-names-change/ - ruclips.net/video/82iSVdr4em8/видео.html If you look at Celia Alves video and look at the Oakely Turvey's comments that will give you lots of methods to try.
I have one issue when I do combine excel file in a folder that there are two sheets (sheet 1 and sheet 2) in a file. But I only need to combine sheet 1. However, when I choose from a folder, it automatically load 2 sheets. How can I remove sheet 2 while transform data? Could you please help? Thank you.
No, I didn’t, and that wasn’t my plan. Solving the header problem is adding too much complexity for a beginners video that is already 24 minutes long. However if you’ve got a headers issue, I’ve got another videos here: ruclips.net/video/82iSVdr4em8/видео.html I show 2 methods, the linked video to Celia Alves shows another method. Plus Oakley Turvey provides another in the comments.
@@ExcelOffTheGrid hello, my question asked, I did not formulate it for you, it was just a comment.. I would like to know your opinion on this content, because your opinion counts a lot for me.. thank you ruclips.net/video/oIkh-xL_leA/видео.html
Marc Thanks for explanation, everything is clear
Thank you very much, Mark. Much appreciated.
You are very welcome👍
Thank you Mark for your amazing video tutorials, I can understand them even if I'm not English speaking (I'm Italian).👍
Good stuff - I’m glad you’re able to follow along.
This is one of the most valuable Excel videos I've seen on GTQ. So thank you, Mark. One snag I ran into as a USA user was the date format, which I think other users may run into. Using the Change type > Using Locale… > Date > English USA will not work if the UK date begins on a day (the first number in the format) above 12. (I'm using Excel 2016 so I know nothing about what newer versions may do in this case.)
My solution was simply to split the columns by delimiter, place the 3 new columns in the order I need them, then merge them as a new date column. Now everything is copasetic.
Thanks for that feedback, I really appreciate it. 😁
The locale setting is not the target format, but the source data format. So, if the dates are a UK format you would use English (United Kingdom).
Your region settings already know you are based in the US. So selecting English (United Kingdom) would convert from UK to US.
Excellent material. Thanks for sharing it!
My pleasure! I'm glad you found it useful.
Very powerful tutorial with easy to understand " basic concept". Thanks for such a superb material and content. Sir may I request you to upload a tutorial explaining multiple headers in PQE. Thanks.
I've already covered that here:
- exceloffthegrid.com/power-query-unpivot-data/
- ruclips.net/video/IofKr6mUIfY/видео.html
Great. Many thanks, Mark.
You're welcome Ian.
Super video. Thanks Mark👍
Thanks Kebin 😁
Excellent tutorial❤
Thank you! 😊
Thank you Marc.
Is there any performance difference between those 2 methods?
Nothing significant that I know about. The biggest time issue is the size and number of files. Which is the same for both scenarios.
Thanks Mark for the awesome video 👍 I need help to retrieve Excel files from OneDrive Personal Folder. Thanks
Awesome tutorial Mark!
I have been using method 2 with xlsx files since then.
However I face a performance issue:
I have raw data (about 50-100MB) with hundred thousands of rows.
Every transformation take ages. It takes sometimes hours.
Every time it goes through all rows.
I wonder if it is possible to load the file into the RAM first before I start the transformation. So the loading would go much faster.
Can you advise how to handle huge data?
Great video! But there's no such function on the Mac version of office. Soyou have any suggestions for Mac users?
The Mac version on Excel is a long way behind Windows.
With A Power BI licence you get access to Data Flows, which is Power Query in the cloud. These can be refreshed through Excel Online. So that is an option.
Or get really good at VBA or Python.
Would this also work if combining excel workbooks if there were subfolders in the import folder
Yes, the default uses sub-folders.
Sir, You did not explain how to solve the inconsistancy in column names.
I've already covered that here:
- exceloffthegrid.com/rename-columns-in-power-query-when-names-change/
- ruclips.net/video/82iSVdr4em8/видео.html
If you look at Celia Alves video and look at the Oakely Turvey's comments that will give you lots of methods to try.
I have one issue when I do combine excel file in a folder that there are two sheets (sheet 1 and sheet 2) in a file. But I only need to combine sheet 1. However, when I choose from a folder, it automatically load 2 sheets. How can I remove sheet 2 while transform data? Could you please help? Thank you.
sorry, but you have not solved the problem of the inconsistency of the headers
No, I didn’t, and that wasn’t my plan.
Solving the header problem is adding too much complexity for a beginners video that is already 24 minutes long.
However if you’ve got a headers issue, I’ve got another videos here:
ruclips.net/video/82iSVdr4em8/видео.html
I show 2 methods, the linked video to Celia Alves shows another method. Plus Oakley Turvey provides another in the comments.
@@ExcelOffTheGrid hello, my question asked, I did not formulate it for you, it was just a comment.. I would like to know your opinion on this content, because your opinion counts a lot for me.. thank you
ruclips.net/video/oIkh-xL_leA/видео.html