I hope you enjoyed this tutorial 😃 If you did please give it a LIKE... and don’t forget to SUBSCRIBE for even more content! For More 👉 czcams.com/users/EssentialExcel
THis the bestest of the bestest solution for Power Query to dynamically to up your latest folder dynamically without having to keep changing the source name!!!!
Great video, and what i was looking for, the only thing i am now trying to work out is how do i incorporate the relative folder (movable folder ) into this? so it will refer to the folder no matter where it is, instead of a defined location?
Keep up the good work, QQ im new to power query and im trying to find a way that i just get the top 6 files from a folder ( this is where this video helped perfectly) but the next part im stuck with is that i need pull specific sheet names, my sheet names are just dates in this format 01.01.2022 for example and i need it to pull if the date was yesterday. Is this possable in power query?
Thank you! This is certainly possible with a number of ways to achieve the same result. You could filter on name using a calculation (within the filter) to format yesterdays date to match your file naming style. You could add new calculated column to do similar test to match file name to yesterdays date and filter on that column. If stuck I could take a look in morning and share a suggestion with you 🙂
Thank you again, i did it slighty differently in the end i managed to do it with a series of filters, it also kept referencing a sheet name which i edited to allow it to be open instead. Really appreciate your response. I look forward to your future videos.
I have to try this out on my self first but I quite dont understand the binary stuff after sorting by date the files in the folder... to me if you sort the folder files by date, the Power Query commands would be dynamic, isnt it ?
it will pick up the top row, but when you expand your binary file, it will hardcode your file name and you have to keep on changing your file name everytime. This binary.combine - will pick up the latest folder and expand your contents of your table without having you to change the source name of your file everytime
Hi, new subscriber here; Background: i have a master spreadsheet at work, the sheet has thousands of rows and dozens of columns. 1 of the main columns has the names of a significant number of staff members (which are not in order because rows have been gradually added over time to represent that staff member - which brings me to my question) Question: i want these staff members to fill in the subsequent columns that belong to them all the way up and down the spreadsheet - BUT without seeing the overall spreadsheet that contains the other columns for the other staff members entering their data - is there a way perhaps to do this?
Hi Noel and thank you for subscribing! 😃 If the data is sensitive to each member then I don’t think there is a secure way I would recommend for doing this in Excel. One solution could be to use Forms. You could then send staff members the link to the form from which they can then enter and submit required data. This is a previous video we did using forms: czcams.com/video/nX9oEsFJpm4/video.html
I hope you enjoyed this tutorial 😃 If you did please give it a LIKE... and don’t forget to SUBSCRIBE for even more content!
For More 👉 czcams.com/users/EssentialExcel
THis the bestest of the bestest solution for Power Query to dynamically to up your latest folder dynamically without having to keep changing the source name!!!!
This video was so helpful and clearly explained. Thanks so much!
Thank you so much! I used this on PowerBI to get the latest file depending on the Date from a SharePoint Online folder, now its working!
Oh my, you saved my computer from being thrown out the window. Thank you so much!🎉
Great. Thanks.
Great help...Thanks
Thanks for this. @6:30 does the Binary.Combine function only work on csv files or would it work on .xslx files too?
Thanks!
Very helpful! Ran into the exact issue from the beginning where it "locked" the file name from when I created the query. Easy fix!
Thank you for such a detailed video. How does this work on a xlsx file? It doesn’t seem to import the data on the binary content step
thanks, you saved me :)
Great video, and what i was looking for, the only thing i am now trying to work out is how do i incorporate the relative folder (movable folder ) into this? so it will refer to the folder no matter where it is, instead of a defined location?
Massive help thank you
Thanks Matt, glad it helped! 🙂
Keep up the good work, QQ im new to power query and im trying to find a way that i just get the top 6 files from a folder ( this is where this video helped perfectly) but the next part im stuck with is that i need pull specific sheet names, my sheet names are just dates in this format 01.01.2022 for example and i need it to pull if the date was yesterday. Is this possable in power query?
Thank you! This is certainly possible with a number of ways to achieve the same result.
You could filter on name using a calculation (within the filter) to format yesterdays date to match your file naming style.
You could add new calculated column to do similar test to match file name to yesterdays date and filter on that column.
If stuck I could take a look in morning and share a suggestion with you 🙂
Thank you again, i did it slighty differently in the end i managed to do it with a series of filters, it also kept referencing a sheet name which i edited to allow it to be open instead. Really appreciate your response. I look forward to your future videos.
@essentialexcel . I cannot figure a way to not mess up my query if the sample file is gone. How can I fix this?
Hi I tried this however when I type binary. , no “suggestions” show up ? Do you know how to solve?
I have to try this out on my self first but I quite dont understand the binary stuff after sorting by date the files in the folder... to me if you sort the folder files by date, the Power Query commands would be dynamic, isnt it ?
it will pick up the top row, but when you expand your binary file, it will hardcode your file name and you have to keep on changing your file name everytime. This binary.combine - will pick up the latest folder and expand your contents of your table without having you to change the source name of your file everytime
Hi, new subscriber here;
Background: i have a master spreadsheet at work, the sheet has thousands of rows and dozens of columns. 1 of the main columns has the names of a significant number of staff members (which are not in order because rows have been gradually added over time to represent that staff member - which brings me to my question)
Question: i want these staff members to fill in the subsequent columns that belong to them all the way up and down the spreadsheet - BUT without seeing the overall spreadsheet that contains the other columns for the other staff members entering their data - is there a way perhaps to do this?
Hi Noel and thank you for subscribing! 😃
If the data is sensitive to each member then I don’t think there is a secure way I would recommend for doing this in Excel.
One solution could be to use Forms. You could then send staff members the link to the form from which they can then enter and submit required data.
This is a previous video we did using forms:
czcams.com/video/nX9oEsFJpm4/video.html
This only works on CSV file. How about for xlsx file?
but how Import ONLY The Latest two File-using power query
Keep 2 rows, top 2 rows would remain.
This method deletes files... wow... thanks.
Hi John, if you could explain the issue further will try and help… but this method of returning file content alone will not delete files.