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
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👍
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!
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
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?
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"?
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
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☺️
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!
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.
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 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.
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.
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 ua-cam.com/video/4xpHN_V8jcM/v-deo.html
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.
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.
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.
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.
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
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 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
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?
This is the exact same issue I am running into. Did you ever find a solution?
@@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
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👍
I have dates in my table. How can I Filter for a specifit date which I mentioned in a cell (Same as Olivia" in your example)
Same question here
BTW, what if I wanna list the all without filters 🤔?
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!
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
Can I use the function into a Json.Document(Web.Contents ... script? Want to have a dynamic value in there.
In case I want to expand the same parameter to another sheet, how would I do?
im using same method but the table is returning empty. even the formatting is also same as text.
Why it doesn't work when I want to have a table with numbers (instead of names)?
Doesn't seem to work with numeric values, only text.
This video has saved me a ton of time. Thank you! 🤩
thank you!
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 get the same thing. Investigating now...
You found any solution to this?
Wuahhh, this is genius! Thanks for sharing
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"?
use or
[rep] = "Olivia" or [rep] = "Grace"
If you have a big list you can use a List to filter it.
This was great! One of the best tutorials I have seen on any Power Query topic. Great for building a foundation to learn! Thank you.
I recommend watching this one if you truly want to have better understanding of Power Query ua-cam.com/video/4xpHN_V8jcM/v-deo.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.
I followed the steps but didn't work me. The function when invoke show's the cell value but my table is empty.
Awesome.
It's not that easy to find good tutorials about advanced PQ and it's not easier in Excel.
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
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☺️
🙂
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!
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.
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?
Sure. Watch this to understand how to do that ua-cam.com/video/4xpHN_V8jcM/v-deo.html
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.
Sure, why not.
Exactly what I was looking for! Thank you
Packed with many valuable learning in this small exercise 😊
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.
You can download this and use in Excel docs.google.com/spreadsheets/d/1zTGEMAKLoRqpbmmUmEo6K1ZFchWwnfVAa42IvqsGSfw/edit?usp=sharing
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 ua-cam.com/video/4xpHN_V8jcM/v-deo.html
is there any change of you covering big query of google sheet? since google sheet has a limit data volume, it would be woth it.
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.
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.
👍
Exactly what I was looking for!
I tried using the same function or parameter in a different sheet and was all blank, any idea?
when you say sheet do you mean tab or file?
Amazing! Just what I needed! Thanks
Why is there no queue function in Excel? It is useful when used with Google Sheet
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.
How about this is value instead of string in our query?
What's the difference?
Perfect, thank you!
Fantastic video. Thank you so much. Helps me a lot
👍
Thanks a lot you're an angel
This is great! thanksa lot for the super clear step by step explanation. Congratulations!
👍
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 found a way to create the parameter using Drilldown. It doesn't use a Table.ToList but it works.
Cool!
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: ua-cam.com/video/9hDkI3iNeqc/v-deo.html At the 7:00 mark he reaches the part that may help you.
GOOD! Thank you for your explanation
👍
Do you know SQL . If yes , wanna learn from you
Noted.
sempre molto bravo nella spiegazione
this is excellent. thanks
thank you
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
If I understand your question correctly, then no. You can't get separate outputs from one query.
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 think the current development of M Language is
Multiple Inputs -> One Output
Which I agree is annoying.
I think JavaScript/TypeScript is much more likely to replace VBA than this.
Thanks!
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?
What is the issue?
@@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.
@@ClaudeBalleux When you connect to the table, choose that column and on top of the menu there are data types. Choose the right type you want to use.
After that convert it to a list.
@@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 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
🙂