Видео

@@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.

@@Environomics. of course, it doesn't. :)

@@zuzanasmekalova4632 glad that it helped. Thank you for watching and leaving your feedback.

Thanks! Glad that it helped. 😊

You're welcome! Thank you for stopping by. 😊

Awesome! Thank you for stopping by. :)

You're very welcome! Glad that it helped and thank you for stopping by.

You're welcome, Michael! Glad that it helped.

You're very welcome! Thank you for stopping by. Glad to help.

You're welcome! : ) thank you for stopping by!

@@CeliaAlvesSolveExcel You're welcome !! : )

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.

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

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.

Great! Glad that I could help.

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

You're very welcome, kind Human Being! 😊 Yes, I have referred back to this video myself quite a few times. 😉

I would say you will have to find a way to identify what kind of data point appears in each row.

Awesome! Thank you for watching till the end, Christy. I hope it was helpful to you.

You're very welcome, Daniel. Glad that it helped.

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

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

There you go! :)

See the video description

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.

Thank you :)

Awesome! And thank you for letting me know. Your feedback brightens my day. 😊

Yes, it is. It will return you the last three months of data depending on the date of the day you run the process.

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

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.

You're very welcome! :)