![Celia Alves - Solve & Excel](/img/default-banner.jpg)
- Видео 137
- Просмотров 793 908
Celia Alves - Solve & Excel
Канада
Добавлен 1 май 2017
In this I share my best techniques to automate manual work in Excel. Tips can be about Excel features, Power Query, VBA, Power Pivot and best practices. If you are interested in taking advantage of all that I have to share, check this page:
snapreportschamp.com/course
Come learn how to automate your Excel Reports in minutes instead of hours without copy-paste or coding:
snapreportschamp.com/course
Come learn how to automate your Excel Reports in minutes instead of hours without copy-paste or coding:
Refreshing twice in Power Query in Excel? Why? Fix it now.
If you need to hit Refresh All twice to get your Power Query queries to fully update, this video shows a possible reason for that to happen and how it can be fixed.
And it is NOT about the Enable Background Refresh property.
The last 10 minutes contain two extra bonus tips to help you refine your tools and make your Excel applications even more robust and efficient.
One thing is to start to use Power Query and automate data importation and transformation with it. Anyone can do it, and if you never tried it, start now!
The next step is to understand what happens behind the scenes when we build multiple queries in one solution and how to ensure that the procedure is the most efficient possibl...
And it is NOT about the Enable Background Refresh property.
The last 10 minutes contain two extra bonus tips to help you refine your tools and make your Excel applications even more robust and efficient.
One thing is to start to use Power Query and automate data importation and transformation with it. Anyone can do it, and if you never tried it, start now!
The next step is to understand what happens behind the scenes when we build multiple queries in one solution and how to ensure that the procedure is the most efficient possibl...
Просмотров: 5 566
Видео
Comparing Two Lists in Excel with Functions - all Power Query Merge Joins - T0033
Просмотров 3,2 тыс.Год назад
Having to compare two lists of items in Excel is a very common task regardless of the industry you work in. In this video, we will explore seven ways of comparing two lists in Excel by looking at how we can get: -the common items in the two lists -the items that only exist in one of the tables -all the items in one list and the corresponding information from the second list (table) for items th...
The best way to create Dependent Dropdown Lists in Excel
Просмотров 737Год назад
You can learn how to do this by watching my previous 30-minute tutorial, where I explain how to set dropdown lists in two columns 👇 ruclips.net/video/v6eT4JlKbS4/видео.html OR you can skip the training and get a READY-TO-USE FILE prepared for 2 and 3 columns with dependent dropdown lists 👇 solveandexcel.ca/dynamic-dependent-dropdown-lists-in-excel/ All you will have to do is to enter your items...
Dynamic Dependent Dropdown Lists in Excel: create and manage all the categories you need - T0032
Просмотров 25 тыс.Год назад
This video shows how to create dependent Dropdown lists in Excel to easily add new categories and subcategories and have them immediately available in the dropdown lists. IF YOU PREFER A READY-TO-USE SOLUTION, get your files here and skip the tutorial: solveandexcel.ca/dynamic-dependent-dropdown-lists-in-excel/ Dropdown lists are a fundamental feature to ensure clean data input in Excel. When a...
Combining text from cells in the same column in Power Query without combining the whole rows T0031
Просмотров 6 тыс.Год назад
When cleaning data in Power Query, it is quite common to merge text from cells in the same row. What about if we need to merge text from cells in the same column while maintaining the values in the other cells belonging to the same row? Learn how to AUTOMATE YOUR EXCEL REPORTS in minutes instead of hours without copy-paste or coding: snapreportschamp.com/course Get this ready-to-use Excel solut...
How to combine in a folder multiple files with different column headers - T0030
Просмотров 34 тыс.2 года назад
How to combine in a folder multiple files with different column headers - T0030
How to rename columns in Power Query that change the name on each data updating - T0029
Просмотров 22 тыс.2 года назад
How to rename columns in Power Query that change the name on each data updating - T0029
How to combine pivoted data from multiple sheets in an Excel file
Просмотров 1,4 тыс.2 года назад
How to combine pivoted data from multiple sheets in an Excel file
Ways of recycling work in Power Query and saving time T0028
Просмотров 9 тыс.2 года назад
Ways of recycling work in Power Query and saving time T0028
Easy way to import messy CSV files into Excel The importance of defining data types in Power Query
Просмотров 8 тыс.2 года назад
Easy way to import messy CSV files into Excel The importance of defining data types in Power Query
How to unstack data in repeated groups of rows in the same column with Power Query in Excel
Просмотров 7 тыс.2 года назад
How to unstack data in repeated groups of rows in the same column with Power Query in Excel
Find out where you can use Power Query and the data sources it can connect to
Просмотров 6492 года назад
Find out where you can use Power Query and the data sources it can connect to
How to make Power Query deal with a variable number of columns in a text source file
Просмотров 4,5 тыс.2 года назад
How to make Power Query deal with a variable number of columns in a text source file
When does it make sense to use Power Query in Excel?
Просмотров 1,6 тыс.2 года назад
When does it make sense to use Power Query in Excel?
How to import data from sheets in the same Excel workbook as the Power Query query
Просмотров 25 тыс.2 года назад
How to import data from sheets in the same Excel workbook as the Power Query query
Why you must learn Power Query if your work involves importing data into Excel
Просмотров 4742 года назад
Why you must learn Power Query if your work involves importing data into Excel
Simplify M language code by eliminating hashtag sign and double quote characters from step names
Просмотров 8912 года назад
Simplify M language code by eliminating hashtag sign and double quote characters from step names
How to prevent data entry errors in Excel with a dynamic Data Validation Dropdown List - T0025
Просмотров 9762 года назад
How to prevent data entry errors in Excel with a dynamic Data Validation Dropdown List - T0025
How to connect to an Excel sheet with Power Query without importing extra columns or rows - T0024
Просмотров 4,8 тыс.2 года назад
How to connect to an Excel sheet with Power Query without importing extra columns or rows - T0024
How to sort data in an Excel Table following any sorting criteria - T0023
Просмотров 2,5 тыс.2 года назад
How to sort data in an Excel Table following any sorting criteria - T0023
Things to keep in mind when connecting to an Excel sheet with Power Query
Просмотров 6132 года назад
Things to keep in mind when connecting to an Excel sheet with Power Query
Two automations to share Power Query results without allowing to refresh the query - T0022
Просмотров 4,9 тыс.3 года назад
Two automations to share Power Query results without allowing to refresh the query - T0022
How to make Table.Combine include extra column from the previous query step
Просмотров 3,6 тыс.3 года назад
How to make Table.Combine include extra column from the previous query step
How to combine Tables using the M Language Function Table.Combine in Power Query
Просмотров 7 тыс.3 года назад
How to combine Tables using the M Language Function Table.Combine in Power Query
Why it is important to save the data source file before importing data with Power Query
Просмотров 4293 года назад
Why it is important to save the data source file before importing data with Power Query
Define the portion that you want to print in Excel using Page breaks and print selection
Просмотров 2303 года назад
Define the portion that you want to print in Excel using Page breaks and print selection
Importing data from external sources with Power Query directly into a Pivot Table in Excel
Просмотров 3,4 тыс.3 года назад
Importing data from external sources with Power Query directly into a Pivot Table in Excel
M Language Tips to achieve more with Power Query - T0020
Просмотров 12 тыс.3 года назад
M Language Tips to achieve more with Power Query - T0020
How to add a File Picker in Excel to select the data source file for Power Query - T0019
Просмотров 5 тыс.3 года назад
How to add a File Picker in Excel to select the data source file for Power Query - T0019
How to combine pairs of rows in Power Query with different criteria per column - T0018
Просмотров 5 тыс.3 года назад
How to combine pairs of rows in Power Query with different criteria per column - T0018
I have put all your code into my VBA Project and edited the path, but when I open it, it says File not found, run time 53: My code : Const FileControlFolder As String = "Z:\IT-Hai\FilesInUse\Test.xlsm"
@@HaiNguyen-ib1wc it's been a long time since I created this solution, so I do not remember all the details by heart. But it seems that FileControlFolder should indicate a path to a folder, not to a file.
why dont you start with showing a basic single query to be copied then move to different if/but conditions
My problem is when the query in one excel sheet gets updated, it doesnt get updated automatically in the mastersheet that has a copy of the query.
@@Environomics. of course, it doesn't. :)
This is utterly useful, thank you so much!
@@zuzanasmekalova4632 glad that it helped. Thank you for watching and leaving your feedback.
it works nice for files on my hd, but I m facing troubles doing this with files saved in onedrive and also with folders on my hd. any solutions for this?
Solved a nagging issue. Thank you!!
Not working
Brilliant
Mam, i do not get any recommended formula
Excellent explanationa and solution 👍❤
Thanks! Glad that it helped. 😊
Hey, I've only recently subscribed to your channel. I love how short your vids are. Ideal to find quick solutions on the job. Its between StackOverflow and Solve & Excel. YT Shorts will be a nice touch, helps me learn something while doom scrolling. Regards, PowerApps newbie.
Thanks a lot - Bravo for the simple presentation
You're welcome! Thank you for stopping by. 😊
You are genius!
love this!
Awesome! Thank you for stopping by. :)
This is amazing, I've spent countless hours trying to fix this problem. Thank you so much!
You're very welcome! Glad that it helped and thank you for stopping by.
So easy!!! Short and to the point. Excellent🏆 Thank you Celia!
You're welcome, Michael! Glad that it helped.
Thank you very much for sharing your expertise
You're very welcome! Thank you for stopping by. Glad to help.
Awesome !! Straight to the point & quick. Thank you !! : )
You're welcome! : ) thank you for stopping by!
@@CeliaAlvesSolveExcel You're welcome !! : )
Hi Celia, thanks for your videos, very informative, quick question, do you know how to obtain the LOCAL Name on instead of the Sharepoint URL when files are stored in Onedrive?
Nice...
Now when I run a Query, Option does not show me that I am loading the data as a pivot table. I have to load the table and then make it pivot. Does this need to update my Excel or is this option locked and needs to be opened?
You define the loading destination when you create the query and load it for the first time. After that, you no longer get asked how to change it. To load as pivot table instead of table, right click on the query on the queries and connections pane in Excel and then choose "Load To" and switch to pivot table. Note that your table will disappear.
on my system while xlookup working properly on his system at same file. # value error is showing on my system
Hard to help without looking at the file. See if this helps support.microsoft.com/en-us/office/how-to-correct-a-value-error-in-the-vlookup-function-1fabc766-32ae-4f7f-a2c4-d095153e6894?WT.mc_id=M365-MVP-5003849
Hi Celia, in my case, I am able to refresh excel table via powerquery triggered with VBA (It is created to save file with connection only tables) but it does not work for my colleague- he still sees template file which contains old data. And yes, I checked "enable background refresh" and it is same(unclicked) for both of us. He is able to refresh template file manually so it is also not related with permissions. What would you recommend in this case? Thank you in advance.
Hello. I am having a hard time to understand your case. If you want to give me more context, I can give it a try. What do you mean by "connection only tables"? When we load a query as connection only it means we do not load the resulting table of that query. So "connection only table" is a conflicting concept to me. :)
@@CeliaAlvesSolveExcel Hi Celia, I meant Connection Only Queries so it is basically same.. Let me explain what VBA does: it opens template file- fills setting sheet so that it can refresh SQL server to get corresponding data, then it removes links and save as another excel file. Therefore, created excel file has tables/queries indicating 'Connection only'... Here my question comes.. I can do this process without any issue but my colleague creates excel file which shows same data in the template file so it does not refresh it... What should we check for this issue except background refresh?
Connection only queries and connection only tables are NOT the same. A connection only query does not generate a table. What you mean is that you remove the connection to the query from the table after refreshing the query. I think it has to do with VBA running faster than power query. Sometimes simply turning off the Enable Background Refresh property is not enough to solve the issue. First I suggest that you run the VBA code step by step with F8 on your colleague's machine to find out where the issue might be happening. These video and blog post may help: ruclips.net/video/6mPYAmiu80g/видео.htmlsi=vWaiL0po_Ge14ss_ solveandexcel.ca/2023/06/23/how-to-make-vba-wait-for-power-query/ Good luck.
Love it!!! This is exactly what I was looking for
Great! Glad that I could help.
Hi in my case i am trying for previous 3 days but it's not working. Please make video on. Days option
Get today's date and then use the function Date.AddDays to calculate 3 days before today. learn.microsoft.com/en-us/powerquery-m/date-adddays
This is one of the best tricks in Power Query, and almost nobody I know, knows it! This is very useful in a lot of scenarios, thank you a lot Celia!
You're very welcome, kind Human Being! 😊 Yes, I have referred back to this video myself quite a few times. 😉
HI Celia, this is really useful thank you! What happens if you're stacked rows are UNEVEN? How would you account for inconsistent data?
I would say you will have to find a way to identify what kind of data point appears in each row.
EUREKA!!!!
Awesome! Thank you for watching till the end, Christy. I hope it was helpful to you.
Thanks for taking your time to put this together.
You're very welcome, Daniel. Glad that it helped.
hello! i have error after doing that Expression.Error: We cannot convert a value of type List to type Text. Details: Value=[List] Type=[Type] HELP PLS
Sorry, having just the error message is not enough for me to be able to help you.
@@CeliaAlvesSolveExcel I did the same things and after replacing in advance settings “path name” I got that error message . But I’m working in one drive, so I got cell name as link. How can I handle with links? Because when input file path name with hands I got usual C:/ and etc
Thank you for this wonderful video. I do have a situation that I can’t find a solution. What I’m trying to do is find a way to highlight all changes that happen between a power query refresh. I can add more detail but I don’t think it is appropriate in commenting on this video
That's a simple wish with not so simple solution. Depending if your scenario, there can be multiple ways of solving it. None is very straight forward. I'll add some resources in the next comments for your to try.
exceleratorbi.com.au/self-referencing-tables-power-query/
ruclips.net/video/6mPYAmiu80g/видео.htmlsi=Jq9dubj--V2XOejP
ruclips.net/video/AijmQ5uuwGw/видео.htmlsi=idQUpUYFUza3pQjg
Just what I needed for my pivotchart too!
There you go! :)
Thanks, could you also provide link to download spreadsheet used in this tutorial
See the video description
Need this for Google Sheets! Using a Named Range in Google Sheets doesn't respond the same as Excel.
Cynthia look through the comments and find Wyn Hopkins video that I mention. He simplified this technique in a way that may work for Google Sheets.
I like the examples!
Thank you :)
Finally! I've tried other methods to map the headers when importing files with tables already defined without joy - Your method cracked it - thanks! 😁
Awesome! And thank you for letting me know. Your feedback brightens my day. 😊
To mach bla bla bla
is this process dynamic ?
Yes, it is. It will return you the last three months of data depending on the date of the day you run the process.
Toooo Boring same thing to many repetative
TOOOOO Lengthy ....lost focus
Eu abri o video que me apareceu , porque procurava a ferramenta para comparar as listas e ouvindo o sotaque , comecou me a entrar a impressaro que a voz era Portuguesa . Fui olhar para o nome do autor e acertei . Celia Alves. Sobre o conteudo , e muito bom . Bem explicado . Obrigado
Since this video was published, this technique was simplified. Watch this session to learn a much easier way to implement up to 3 dependent dropdown lists. ruclips.net/video/9YzxMuih_7E/видео.htmlsi=3EUWBU9l8wdAscIj
Hello Celia, I inserted my dropdown lists in between other columns and noticed that if I apply filters; I lose the content of my dropdown lists...is there a way to apply filters wo losing them? Thanks
Hi, Nelson. I tested on my end and I do not get that behavior. I wonder if it had to do with filters at all. I recommend checking the formulas associated to the data validation. The $ need to be in the correct place. Give it a review and good luck. Thank you
@@CeliaAlvesSolveExcel , Thank you for reviewing this. 1. Found an error on the first data validation...I did not roll the formula to all table - this one is solved 2. Since I inserted the data validation formulas between other columns, when I apply filters; I only get them in one side of the columns...example have other data in columns "a & b" then data validation formulas in "c & d" and other data again in "e & f"; can I get them in both sides? Thanks again for your help.
@@Nelson10409 you control where the data validation applies. It all has to do with how you set the rules. Since I published this video, a sonogram technique came along. I suggest that you check the comments to find that other way which is much easier to implement.
Check this one: ruclips.net/video/9YzxMuih_7E/видео.htmlsi=3EUWBU9l8wdAscIj
Magic! Thanks so much for this solution Celia.
You're welcome, Wayne! Glad that it helped. In the meantime, I've evolved to using a simpler technique that does not involve creating names. If interested, please see here ruclips.net/video/9YzxMuih_7E/видео.htmlsi=xBMadi7-K7yBZIuA In this presentation I explain dropdowns from creating one up to creating 3 dependent ones.
Thank you very much that really worked and it was clearly explained too!
Excellent
Thank you
You're very welcome! :)
Thanks for the video but such things only seem to work for the tutors themselves, MS products are a pain when it comes these kind of things. For instance, W11 can't be installed even on a 10th Gen machine due a hardware restriction! Soon Windows will be on subscription, no question. Time to migrate to new ecosystem as Microsoft products are increasingly getting abhorent .
This is very useful and it worked. The way you teach step by step makes the all the difference compared to other videos. Thank you so much!