It would be nice if you showed complicated problem-solving with Power Query, e.g. dealing with a specific column header that has a varying name (e.g. "Date" or "date" or " dates"). There are already a plethora of "I'm assuming you've never used PQ before" videos.
Thanks for the suggestion. We'll add more advanced tutorials on PQ to our list for future videos. That's a great question and there are several ways to handle varying column names depending on the setup of your source data. In the meantime, we do cover some techniques for varying column names in lesson 4.6 of our Power Query Pro Course. There is a link in the description to the course and it's currently on sale for Black Friday.
I downloaded the files to work along with you. When I did the combine section, my Store Data query had 3699 rows. Then I looked and saw that 12 - Pacific City was already in the Store Data folder. lol. So, I just moved it out of the folder and refreshed to get the same basic experience, and then put it back in. Practice, practice, practice. 😃Great video. BTW, I'm thoroughly enjoying your Power Query Pro Course.
Great video! So the key to using PQ to run the same operations is to put the new file in the same folder as the original, with the same file name, correct?
Great question! That's one way to do it. However, there are many other techniques for updating data. The file doesn't necessarily have to have the same file name. You can also include any new files in a folder. Or only use the file with the most recent modified or created date in a folder. There are a lot of options to fit different processes and workflows. We cover more of these techniques in our Power Query Pro Course. If you're interested, we just started our Black Friday offer where you can save 80% on the course. www.excelcampus.com/power-query-pro-course-offer/ I hope that helps. Thanks again and have a nice day! 🙂
Great video ! Instead of removing columns use choose columns so the step can be easily edited in a dialog box if you later want to add or remove columns
For the action of replacing the "old" data that has been cleaned up by PQ with a new data file to be possible, the assumption has to be that the data contents (such as Column names, the number of columns, Capital letters and small letters, the order of the columns) have to be the same as the original data file, with different values. And when we need to keep the original data, we wouldn't want it to be replaced by a new file. If we need to keep the original data file, what should we do, before replacing it with the new file?
There are a couple of options to keep the data in the old file. Move the old file to a different directory, or rename the file. (Without making any changes to your query, power query will take its source data from the same file name in the same directory as it did when you first set up the power query. -So you'll need to make sure the new data you want is a file with the same name, and in the same file path. Second option is that power query has a feature to "change source" - So you can essentially tell power query: "This time, get my data from ." As you pointed out, you would need to make sure that your new data has all the same columns in the same order as the original file. If not, you would have to make extra changes to your query, or copy the query and make the changes in the new query, if you want to keep the old query too.
Great answer, Russell! 👍 And great question, Harumih! I only showed one simple approach for updating the data with a new CSV file in this video. As Russell mentioned, there are several techniques for doing this. A lot depends on your process and workflow. The good news is that Power Query can handle many different scenarios. It can even handle the scenario where column names are not exactly the same in each file or when columns are in different order. We'll add this to our list for future UA-cam videos. And we do cover many of these scenarios in our Power Query Pro Course. There is a link in the description to that. Thanks again! 🙂
@@russelleverson9915 Thank you for your suggestion! I thought of doing so, as you suggested, but I wanted to be sure. Thank you very much for your input!
Question, I do have a file that comes everyday that I need to clean. I imported the .csv into PQ made all the changes and saved the file. When I import a new file that I need to be cleaned into the same directory and open the PQ file and refresh my old data disappears and the data from the new file is not shown. Not sure what I am doing wrong?
I did not understand what you meant by dragging the file to the folder and using refresh. What is the exact purpose of doing that? Please explain in simple terms. Thank you.
Excel started out with a weak model, and then has accreted. With great power comes great incomprehensibility. I wonder how many people can come even close to mastering this monster.
It is a monster indeed! 🙂 I encourage our members and audience to focus on mastering the parts they need, instead of trying to learn every aspect. Now I realize that's not always easy to do. But Power Query is one feature that has the potential to help a lot of users because we (data analysts) tend to spend a lot of time cleansing and preparing data. We do have a free training webinar that covers all of the modern (and old) Excel features and how they fit together. It's called Excel Blueprint and there is a link in the description. I hope that helps. Thanks again and have a nice day! 🙂
I agree! Power query has saved me so must have time! The power query course is excellent.
Thanks so much for your support, Angela! It's been exciting to see how much you've accomplished at your job with PQ. 🙌
It would be nice if you showed complicated problem-solving with Power Query, e.g. dealing with a specific column header that has a varying name (e.g. "Date" or "date" or " dates").
There are already a plethora of "I'm assuming you've never used PQ before" videos.
Thanks for the suggestion. We'll add more advanced tutorials on PQ to our list for future videos.
That's a great question and there are several ways to handle varying column names depending on the setup of your source data.
In the meantime, we do cover some techniques for varying column names in lesson 4.6 of our Power Query Pro Course. There is a link in the description to the course and it's currently on sale for Black Friday.
like👍
I will watch this video few more time😊
thanks❤
Thanks Jessica! 🙌
Wish my company used power query ....
PQ is life changing indeed!
Though I've been using power query for more than 2 years, your tutorial is still amazing for me. Thanks.
Great to hear that! 😀
A great video Jon. Thank u for ur hard work
Thanks Nader! 🙌
Hello Sir. Thank you so much.
I just want you to know that you literally saved my life with your favourite feature of Power Query!
Amazing Content! ❤
Glad I could help. 😀
I downloaded the files to work along with you. When I did the combine section, my Store Data query had 3699 rows. Then I looked and saw that 12 - Pacific City was already in the Store Data folder. lol. So, I just moved it out of the folder and refreshed to get the same basic experience, and then put it back in. Practice, practice, practice. 😃Great video. BTW, I'm thoroughly enjoying your Power Query Pro Course.
Thank you for your videos. They are always helpful to me.
I'm happy to hear it. Thanks for your support! 🙂
Great video! So the key to using PQ to run the same operations is to put the new file in the same folder as the original, with the same file name, correct?
Great question! That's one way to do it. However, there are many other techniques for updating data. The file doesn't necessarily have to have the same file name.
You can also include any new files in a folder. Or only use the file with the most recent modified or created date in a folder. There are a lot of options to fit different processes and workflows.
We cover more of these techniques in our Power Query Pro Course. If you're interested, we just started our Black Friday offer where you can save 80% on the course. www.excelcampus.com/power-query-pro-course-offer/
I hope that helps. Thanks again and have a nice day! 🙂
@@ExcelCampus What a nice and detailed response -- thank you. :) Very impressed by that. I will check it out :)
Thanks for letting me know. We aren't always able to answer every question, but I'm happy to hear it was helpful. 🙏🙂
Thank you so much! Very helpful video
Glad it was helpful! 😀
Where did you get the “mouse” pic you use when you display left or right click?
Thank you Jon.
Appreciate your support, Mark! 🙏
Great video ! Instead of removing columns use choose columns so the step can be easily edited in a dialog box if you later want to add or remove columns
Informative video...Keep it up!
Thank you! 🙂
Amazing! I love Excel.
🙌 me too!
Just amazing!
Excellent simple and easy to understand video, thank you!
Thanks for the feedback! 😀
Splendid
Fantastic!
Dataset To Link?
Very nice!! I actually could follow along!!!
Thanks! 😀
For the action of replacing the "old" data that has been cleaned up by PQ with a new data file to be possible, the assumption has to be that the data contents (such as Column names, the number of columns, Capital letters and small letters, the order of the columns) have to be the same as the original data file, with different values. And when we need to keep the original data, we wouldn't want it to be replaced by a new file. If we need to keep the original data file, what should we do, before replacing it with the new file?
There are a couple of options to keep the data in the old file.
Move the old file to a different directory, or rename the file. (Without making any changes to your query, power query will take its source data from the same file name in the same directory as it did when you first set up the power query. -So you'll need to make sure the
new data you want is a file with the same name, and in the same file path.
Second option is that power query has a feature to "change source" - So you can essentially tell power query: "This time, get my data from ."
As you pointed out, you would need to make sure that your new data has all the same columns in the same order as the original file. If not, you would have to make extra changes to your query, or copy the query and make the changes in the new query, if you want to keep the old query too.
Great answer, Russell! 👍 And great question, Harumih!
I only showed one simple approach for updating the data with a new CSV file in this video. As Russell mentioned, there are several techniques for doing this. A lot depends on your process and workflow.
The good news is that Power Query can handle many different scenarios. It can even handle the scenario where column names are not exactly the same in each file or when columns are in different order.
We'll add this to our list for future UA-cam videos. And we do cover many of these scenarios in our Power Query Pro Course. There is a link in the description to that.
Thanks again! 🙂
@@russelleverson9915 Thank you for your suggestion! I thought of doing so, as you suggested, but I wanted to be sure. Thank you very much for your input!
@@ExcelCampus Hi Jon, thatnk you very much for your follow-up! And thank you, too, Russell!
Question, I do have a file that comes everyday that I need to clean. I imported the .csv into PQ made all the changes and saved the file. When I import a new file that I need to be cleaned into the same directory and open the PQ file and refresh my old data disappears and the data from the new file is not shown. Not sure what I am doing wrong?
Where can we find those exercises files? For training.
DOWNLOAD THE WORKBOOK ► www.excelcampus.com/powerquery/automate-5-excel-tasks-power-query/
and changing the refresh settings to auto-refresh when opening the file
thanks
Thanks for sharing ❤
You are so welcome! 😀
Awesome video Jon
Thanks, Yohanna! 😀
Excellent video!
Thank you! 😀
Awesome ❤
Thanks 🤗
sir, how can we paste data in filtered visible cells only that were collected from another table.
Great question! We have a video on this. Here is the link. ua-cam.com/video/mrDhkClPXEM/v-deo.html
I did not understand what you meant by dragging the file to the folder and using refresh. What is the exact purpose of doing that? Please explain in simple terms. Thank you.
Thank you for your question! View 2:11-2:42 in the video. Do this when you get new data that needs to replace the old data. I hope that helps you!
Data keeps changing. And this is an easy way to bring in the new data without repeating all the other steps
Excel started out with a weak model, and then has accreted. With great power comes great incomprehensibility. I wonder how many people can come even close to mastering this monster.
It is a monster indeed! 🙂
I encourage our members and audience to focus on mastering the parts they need, instead of trying to learn every aspect. Now I realize that's not always easy to do. But Power Query is one feature that has the potential to help a lot of users because we (data analysts) tend to spend a lot of time cleansing and preparing data.
We do have a free training webinar that covers all of the modern (and old) Excel features and how they fit together. It's called Excel Blueprint and there is a link in the description.
I hope that helps. Thanks again and have a nice day! 🙂