Extract previous - I didn't know that one. It could save quite a bit of work. I will have to watch the dependency window more often. It helped you make the differences clear. 👍
Extract previous is new to me too 👍.. what I have used though is “copy” the query from the connections pane and pasted into new workbooks - that saves a pile of time if your source and clean strategy are going yo be the same in several files up to a point.
Thanks for answering my question. You got a Subscriber. I also appreciate you providing several solutions with duplicate query, reference query, Extract Previous, and Delete Until End, and Query Dependencies (need to verify with this more often). From your expertise, if I want to routinely clean data with 400K rows or less (TRIM, CLEAN, data type, PROPER, and remove duplicates), SHOULD I use an Advanced Editor script (like VBA) with a little editing? Or open an existing query and change source in settings (don't have to add comma & change source of 2nd line)?
@@txreal2 from an Excel file create a query to that file with the data. Power Query does all that mentioned to clean the data. Save the query and load the data to the Excel file. Next time you have new data, replace the data file and in the Excel file with the query, hit refresh. That's it!
Does it matter (connection & speed, 100K rows x 20 columns mainly Transactions numerical data) if I put Power Query table in a different workbook from the original data? If it does NOT affect speed that much, I can reuse the PQ table and point to a different data source. Thanks (Windows 10 x64, i7, 32GB RAM, OneDrive for Business)
I am not sure how performance compares when we connect to a separate workbook versus getting the data from the current workbook. I expect that connecting to the current workbook is faster, then connecting to another workbook, specially when it is saved to OneDrive or SharePoint. But there are many other factors that will contribute to the query performance. Some examples are: - data source file type: importing data from a CSV file is faster than importing the same data from an Excel file. - columns data type: whole number requires less memory than currency data type. Currency takes less memory than decimal data type. Text data type requires more memory than the previous ones. - torres of transformations used in the query: some transformations require more time to process. Merging and Sorting are two of those transformations. I hope this helps.
Hi, Lily. I'm afraid you'll have to be more specific about your scenario. first thought, I don't understand your question in the context of this video.
I have loaded a data set and applied over 20 steps to clean the data into a usable format. Every month I will receive a new raw data set that these same cleaning steps will need to be applied to. How do I save the query steps and apply them to a new data set. Eg. I will have to load a difference set of data and then clean it. I don't want multiple queries in the same file as they will need to be converted to their own set of pivot tables for each individual client.
Extract previous - I didn't know that one. It could save quite a bit of work. I will have to watch the dependency window more often. It helped you make the differences clear. 👍
Thank you, Gráinne! I am glad you found value in the tutorial. ;-)
@@CeliaAlvesSolveExcel always do Celia
Extract previous is new to me too 👍.. what I have used though is “copy” the query from the connections pane and pasted into new workbooks - that saves a pile of time if your source and clean strategy are going yo be the same in several files up to a point.
That's awesome, Martin! Glad that it helped. Yes, I also like to copy and paste queries from one file to another. :)
Wonderful presentation. I can immediately use the reference which is better than duplicate for my purpose. Now I know better
Awesome! Glad that I helped. :)
Hi Celia, Great video! This is my favorite video that I have seen from you. It is so good that I came back to watch it again. Thanks!!
Thank you! :) I am glad that you found value in this tutorial. Thank you for your feedback.
Thanks for sharing ❤
You're welcome, Frank! My pleasure. :)
Hi Celia. As always.. great tips from your channel! Thanks for sharing these time saving techniques :)) Thumbs up!!
Thank you, Wayne! Thank you also for always watching my videos and providing feedback. :)
@@CeliaAlvesSolveExcel My pleasure! Thanks for so generously sharing your knowledge and insights!!
Omg. Thank you.
This is very uses full
Hi, Santu. Thank you. Yes, you can use these features in many different scenarios.
Thanks for answering my question. You got a Subscriber.
I also appreciate you providing several solutions with duplicate query, reference query, Extract Previous, and Delete Until End, and Query Dependencies (need to verify with this more often).
From your expertise, if I want to routinely clean data with 400K rows or less (TRIM, CLEAN, data type, PROPER, and remove duplicates), SHOULD I use an Advanced Editor script (like VBA) with a little editing?
Or open an existing query and change source in settings (don't have to add comma & change source of 2nd line)?
Glad that you found the tutorial helpful.
I am not sure I follow your question. Is it always the same file, only with new/ updated data?
@@CeliaAlvesSolveExcel Yes, new file with updated data with same column names.
@@txreal2 from an Excel file create a query to that file with the data. Power Query does all that mentioned to clean the data. Save the query and load the data to the Excel file.
Next time you have new data, replace the data file and in the Excel file with the query, hit refresh. That's it!
Thank you for this video
You're welcome, @Boris Noro! :)
@@CeliaAlvesSolveExcel haha i m watching it again one year later ^^
@@wisecatnoroboris hey, Boris! Anytime you want! It is free and available 24/7! 😁 I am glad that you found it helpful.
Thanks. practical and inteligent
Glad you found the lesson valuable. :)
Does it matter (connection & speed, 100K rows x 20 columns mainly Transactions numerical data)
if I put Power Query table in a different workbook from the original data?
If it does NOT affect speed that much, I can reuse the PQ table and point to a different data source.
Thanks (Windows 10 x64, i7, 32GB RAM, OneDrive for Business)
I am not sure how performance compares when we connect to a separate workbook versus getting the data from the current workbook.
I expect that connecting to the current workbook is faster, then connecting to another workbook, specially when it is saved to OneDrive or SharePoint.
But there are many other factors that will contribute to the query performance. Some examples are:
- data source file type: importing data from a CSV file is faster than importing the same data from an Excel file.
- columns data type: whole number requires less memory than currency data type. Currency takes less memory than decimal data type. Text data type requires more memory than the previous ones.
- torres of transformations used in the query: some transformations require more time to process. Merging and Sorting are two of those transformations.
I hope this helps.
@@CeliaAlvesSolveExcel Thanks!
Thank you!
You're welcome! :)
Hi Celia,
please tell us , what to do in same situation as u have shown in the video , but if their is more than 100 sheets
Hi, Lily. I'm afraid you'll have to be more specific about your scenario. first thought, I don't understand your question in the context of this video.
I have loaded a data set and applied over 20 steps to clean the data into a usable format. Every month I will receive a new raw data set that these same cleaning steps will need to be applied to. How do I save the query steps and apply them to a new data set. Eg. I will have to load a difference set of data and then clean it. I don't want multiple queries in the same file as they will need to be converted to their own set of pivot tables for each individual client.
you can connect to the source data from a specific folder that'll do it or, my preference, just copy the queries themselves into a new file.
unable to download exercise files
Did you register on the Telegram channel?
@@CeliaAlvesSolveExcel page is not loading
@@krishnamanjunatha6183 sorry to hear that. Can you please maybe try another browser? It seems to be working well on my end:
t.me/celiaalvessolveexcel