My favourite Power Query Video 🤩. I have spent the past month going through all the power query videos in my watched history (literally hundreds) , trying to find it. All I could remember about it was the ‘function’ element!!! It’s 1st Sep and I found that I had watched it back on the 14th Apr so you can imagine how many videos I’ve rewatched in order to find it. Having watched it again, it was worth every hour I’ve spent looking. Thanks👍
Just save this to my list. In case I need to solve some problem in the future. Just watched your video from 3 years ago about mail merge with Google docs. It helps to solve the problem in my new project. Thank you so much.
Great Video. This is what i was exactly looking for. I used the parameters to query from database using this tutorial. Thanks for uploading this video!
This is great, however, I can't seem to get it to work picking up a date. I've got dates on my worksheet, formatted as dates but when I try using the function to retrieve the date I get the message "We cannot convert the value #datetime(2022, 4, 1, 0, 0, 0) to type Text.". How do I work round this?
@@hpope1130afraid I can't remember what I was trying to use it for now so I don't know if I managed to find a solution or had to do something different to achieve what I was attempting to achieve
Try adding the below as a function. You can use DateTime.ToText or Date.ToText depending on whether it's date or datetime, and the yyyy etc bit is an optional part which you can use to specify the date/time format you want it in after conversion. let Source = () => let Source = Excel.CurrentWorkbook(){[Name="YourName"]}[Content], Column1 = Source[Column1] ,MyList = Column1{0} ,MyListText = DateTime.ToText(MyList,"yyyy-MM-dd HH:mm:ss") in MyListText in Source
great tutorial! I have 3 named ranges used as filters for a table, but I want to not require them all to have values. Applying the code in the video, it causes there to be 0 results when any of the cells are blank. Can you help with how to modify the code to allow for blank values...that way if there are no filters, all records would show in query table. Thanks!
Fantastic 👍🏼. That’ll be GREAT if you could parameters as dependent drop-down lists to make that better functional and to avoid not found results.MOREOVER, you could add a simple button to REFRESH☺️
Can you please let me know how can we use the function in the query in advanced Editor of Power Query or can we access values from cell into the SQL query
Great tutorial, thanks a lot for this. What would be the solution when you have to add /filter for the same parameter but multiple values, let’s say Olivia and Grace"?
I’m trying to create a filter, but the filter could have 1 to several criteria for the same column. Your video illustrates one criteria per column and the position is 0. What would I have to do differently to turn that one criteria into a range of criteria and then use the count of that range for the positions in the list? For example, there may be 2 criteria with positions 0 and 1, then next time there may be 3 criteria with positions 0, 1, and 2. I just can’t figure out how to make those positions dynamic/variable. I saw that someone asked about that earlier and was directed to another video. I watched that, but it didn’t answer my question. Any help would be greatly appreciated!
It should be possible with List Contains learn.microsoft.com/en-us/powerquery-m/list-contains in your filter criteria, where the first argument will be the list of multiple values you have, which you could also dynamically extract from the spreadsheet from a table, and the second argument will be the value in the table row. You'll have to be pretty good with M Language for this though, it won't work with just pushing buttons.
Can I use the dynamic parameter for 6 different sheets in the same workbook? I am building a workbook with 6 tables and I want to do the dynamic parameter in another sheet and set it to connection only.
The Moment I try to use the function I get the following error: Formula.Firewall: Query 'ResultsList' (step 'Filtered Rows') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination. let Source = MergedList, #"Filtered Rows" = Table.SelectRows(Source, each (Text.Contains([Name], getName()) )) in #"Filtered Rows" getName being my function instead of getSalesRep The function itself invokes fine, but as soon as I add it instead of the static value, my query breaks. Any ideas why?
I've tried to record a video on bigquery a couple of weeks ago, and the interface kept giving bunch of errors and kept crashing, as a result most of the video was handling bugs and errors. I've deleted the recording since I figured nobody wanted to watch that. So the final result was 5 hours of my time spent with nothing to show for it, not feeling like repeating that experience again right now.
Surely you do provide the accompanying excel files as well? Where can one find them? I’d like to download and follow along as you are performing the steps.
Is there a way to use a named range (lets call "sales_reps") that has more than 1 item and recall something like: getValue("sales_reps")(0) or getValue("sales_reps")(1) etc. (0,1... are the index numbers) for each rep?
Can you read from a list (more than 1) and give each result in a separate sheet with the sheet named by the name in the list. Like a result sheet for Olivia is named Olivia
You could probably generate query that has all those tables stored as an object in a table, but I'm not aware of any ways of automatically outputting them to different sheets on the spreadsheet without using VBA.
@@ExcelGoogleSheets Okay. Thank you. I was thinking that a loop would work. I can get Power Query to output a result as a table in a new sheet named after the table. If it is to "replace' VBA then it needs that functionality
I'm running into a problem with this. I'm working from a named cell which contains a value and is formatted as Number. I'm getting an error that says "We cannot convert the value 2023 to type Text." I don't see where it's trying to convert to type Text or why it would attempt to do so. The Editor says there are no syntax errors. Here's my code. llet Source = Excel.CurrentWorkbook(){[Name="GFY_input"]}[Content], ChangedType = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}), MyList = Table.ToList(ChangedType), Report_GFY = MyList{0} in Report_GFY The error comes in on the transformation to a List. Under the List header it shows Error.
I'm having the same problem when the named cell is a Number. I've tried formatting it as Text before I bring it over to the Power Query Editor but that doesn't make any difference. It comes over into PQE but when I try to convert it into a list (Table.ToList), I get an error as well. I really enjoy this video but if I can only use text instead of numbers, it causes a very large problem. Please help.
@@mikeguest2763 Try this video: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-9hDkI3iNeqc.html At the 7:00 mark he reaches the part that may help you.
querylanguage was created by Google for other presupposes and then it was introduced in Google Sheets as a part of QUERY function. It was never a thing in Excel.
Hi, thanks for sharing. But, I have some issue to make it. I think this is because the cell I'm using is number type. Could it be the reason? do you know how to fix it?
@@ExcelGoogleSheets I'm sorry. I've just did not follow the steps correctly. Everything is working well with list that contain text value. Thank you. But, with numbers format, I'm receiving this messsage. Sorry, . . .we have not been able to convert the value 10 as text type. I'm receiving this message in French, so I'm translating here.
@@ExcelGoogleSheets OK, it's working. But, from there how can I select the valeu number 0 (the step at 7:33 in the video). Here is what my advanced editors shows me. let Source = Excel.CurrentWorkbook(){[Name="Numérique"]}[Content], #"Type modifié" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}), Column1 = #"Type modifié"[Column1] in Column1
I recommend watching this one if you truly want to have better understanding of Power Query ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-4xpHN_V8jcM.html
Little long for the content but useful as always. I’m trying to use named range as starting and end points of a sheer region I need to grab. Haven’t found it yet. Maybe I will leverage different property besides content.
Thank you, I learn so much from here, could you please make a video about how to put task list from google sheet to google task with google app script? thank you
Great content! Thank you. It was very useful. A question that I have: If I leave the "name_range" cells empty then I get no results in the table, which makes sense. However, it would be very useful in my case to get the whole table (as if it was not filtered) if there is no input in those cells, instead of no table. Is that possible? Thank you!
It's possible using an if statement, but that require to learn a lot more about the language in order to do so. Check out this video to better understand M language ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-4xpHN_V8jcM.html
I have seen a BUNCH of youtube videos in my years. Excel, python, SQL, Power BI etc. etc. your methodical walkthrough and explanation is one of the best 'teachings' I have experienced on youtube :) nice. And super cool functionality too. Coupled with datalavidation on tablerange lookup, with a recorded macro to run update - this I am most definitely going to use. Thank you :9
