Be among the first 100 to jump on board "The Query Editor" newsletter 🚀. It's packed with super-actionable tips and insights. Why should you be excited? 🌟 Because the first 100 subscribers will receive an exclusive 50% discount on my upcoming mini-course, set to drop early next year (or perhaps even sooner)! 🤞Just hit this link to subscribe thequeryeditor.beehiiv.com/subscribe 🚀🔥
I really appreciate how well you explain this process! I have one question. When you're at the "Select which level to apply these settings to" step you say to choose the very last level that points directly to the file. Does it only work if you choose the last level? What would happen if you selected a folder or two above where the file is? (I also need to ask you a question about your video about fixing the Formula.Firewall error, but I'll do that on that video.) Thanks!
Hi @Gilligan2755 I'm really glad the video was helpful! The level you select for the authentication method determines what part of the URL will have the authentication method applied to it. If you select the top level web address, the authentication method you select will be used for that URL address or any sub-address within that address. But you might not want to set the top level address because different sub-addresses can require different authentication methods. For example if you were accessing two separate folders of a single SharePoint site and wanted to use different Microsoft accounts to access each one. After you've set the authentication method for a specific address, you won't need to select the authentication method for that URL address or any sub-address again.
Hello, thank you for creating this tutorial. I was trying to get just the file name for the share point when the file is uploaded to SharePoint is this possible?
Hi you’re welcome. Click on the check box next to the document that’s in SharePoint, then click on the “i” that’s in the circle on the top right, it’s the document information panel, click on that and scroll down to “Path” and there’s the file path to the document in SharePoint so you can click on the copy icon, and that gives you the file path in SharePoint.
Hi Ian, thank you! Here is the link to how to rebuild the data combination, the video is called How to Fix the Formula.Firewall Error: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-SLn5sAL3WnQ.html
Hello Miss Microsoft, your clip și very helpful, unfortunately you only show us how to make it the easy way with the privacy settings change, and the clip with formula firewall has an example where you link files from within you computer and not SharePoint. I have made the connections between the queries and I do not receive the error anymore but there is a different privecy window that pops up when I refresh the query, do you know how to solve this?
I have a unique issue with my data. I have to combine data from last weeks file to this week's file. Each week I filter the list of files and filter to the file that was posted within the last 7 to 10 days. These files have different names that include the date the file was created. When pulling last week's data the query always errors out saying a key didn't match the columns of the table. Is this due to my filter? I really enjoy your videos and look forward to the notifications showing your latest videos.
Hi Jeremy, did the name of last week’s file change? That could be causing the error. Do you mind sending me an example of your files as I would like to see how you’re combining them and filtering them? I’m glad you enjoy the videos!
Yes ma'am the name changes every week. It is a sharepoint file folder populated by my finance department that shows variance per work order. I run 2 queries, one from the new file and the other from last weeks file which are both uploaded to my computer and saved in a file folder. I access the same sheet every week from each file. I have tried filtering to show the new file for one query and last week's file for the other query but the query breaks every week. I am unable to send an example due to my organizations rules for file sharing.
Scenario 1: Let’s say last week’s file has your data that you want in Sheet1, if you’re using the from “Folder” function to combine your files, so you’re using the “Folder.Files” function, then your error in your end query should be something similar to: “An error occurred in the Transform File query. Expression.Error: The key didn’t match any rows in the table.” Then the details of the error will show: key= Item=Sheet1 Kind=Sheet So Sheet1 was hardcoded in last week’s query. This week when the data was pulled, last week’s “Sheet1” may have changed to a different name. So let’s say Sheet1 changed to Sheet2. And the new file for this week also has the data that you want in Sheet2. Go to your Transform file and on the error there should be a “Go to Error” click on that and it should then take you to the Navigation step, and it also opens up the Navigation screen, click on Sheet2 and click ok and your end query should be fixed. So it’s important that your sheet names, or if they are tables that you’re combining, have the same names, and remember Power Query is case sensitive. Scenario 2: if your sheet name for last week was 20230508 and this week data is 20230515 Your error in your end query will also point to the Transform file query and show details: Item=20230508 Kind=Sheet Go to the Transform File query and go to the Navigation step, then go to the formula bar, Your code should show: =Source{[Item=“20230508”,Kind=“Sheet”]}[Data] Change the code to: =Source{0}[Data] So you’re changing the sheet name which was 20230508 to the position of the sheet in your Excel Workbook which should be position zero in your workbook. I will make a video on this also. Hope this helps in the meanwhile. Let me know?
Well the sheet doesn't chang. The sheet has the same name but the file has a different name. Do you think that is what is causing my error? I am connecting to sharepoint when pulling this file, is this error being caused by sharepoint?
Hi, what if the Source File is used for the Merge + Append + Group transformations with other files . but now the source file got Updated with Lots of Data and it is difficult to update the file and we need to replace the source file alltogether, with new file with same name, without affecting the previous output. Can it be done with "Parameters" Setting or something else. Please Advise.
Hi, please watch this video here I show you how to refresh your query with the new source file that has the same name as the previous file and how to keep the previous file’s data as well when you refresh: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-kAPqr8DCOng.html
Hello Miss, well done on teaching us well, I really appreciate how you teach .. My challenge is related to this and I have stressed my memory to the level I could...I don't know if you can message me via WhatsApp to help me out or any channel of your choice please 🙏
