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 was great! One of the best tutorials I have seen on any Power Query topic. Great for building a foundation to learn! Thank you.

@ExcelGoogleSheets

Жыл бұрын

I recommend watching this one if you truly want to have better understanding of Power Query kzread.info/dash/bejne/ZqykqrCYhpvQk7A.html

Amazing! Just what I needed! Thanks

Packed with many valuable learning in this small exercise 😊

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?

Exactly what I was looking for! Thank you

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.

@ExcelGoogleSheets

Жыл бұрын

👍

Exactly what I was looking for!

This is great! thanksa lot for the super clear step by step explanation. Congratulations!

@ExcelGoogleSheets

Жыл бұрын

👍

Perfect, thank you!

sempre molto bravo nella spiegazione

this is excellent. thanks

Fantastic video. Thank you so much. Helps me a lot

@ExcelGoogleSheets

Жыл бұрын

👍

GOOD! Thank you for your explanation

@ExcelGoogleSheets

Жыл бұрын

👍

BTW, what if I wanna list the all without filters 🤔?

Thanks a lot you're an angel

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☺️

@ExcelGoogleSheets

Жыл бұрын

🙂

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

@ExcelGoogleSheets

Жыл бұрын

🙂

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)

@andersonsandoval8424

Жыл бұрын

Same question here

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?

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

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!

@ExcelGoogleSheets

Жыл бұрын

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 kzread.info/dash/bejne/ZqykqrCYhpvQk7A.html

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!

@ExcelGoogleSheets

Жыл бұрын

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.

Why it doesn't work when I want to have a table with numbers (instead of names)?

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

im using same method but the table is returning empty. even the formatting is also same as text.

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.

@ExcelGoogleSheets

Жыл бұрын

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.

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"?

@ExcelGoogleSheets

12 күн бұрын

use or [rep] = "Olivia" or [rep] = "Grace"

@ExcelGoogleSheets

12 күн бұрын

If you have a big list you can use a List to filter it.

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.

@ExcelGoogleSheets

Жыл бұрын

Sure, why not.

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?

@ExcelGoogleSheets

Жыл бұрын

Sure. Watch this to understand how to do that kzread.info/dash/bejne/ZqykqrCYhpvQk7A.html

Awesome. It's not that easy to find good tutorials about advanced PQ and it's not easier in Excel.

I followed the steps but didn't work me. The function when invoke show's the cell value but my table is empty.

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.

@ExcelGoogleSheets

Жыл бұрын

You can download this and use in Excel docs.google.com/spreadsheets/d/1zTGEMAKLoRqpbmmUmEo6K1ZFchWwnfVAa42IvqsGSfw/edit?usp=sharing

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?

@jamiemarshall8284

Жыл бұрын

I get the same thing. Investigating now...

@eladushkegm

Жыл бұрын

You found any solution to this?

I tried using the same function or parameter in a different sheet and was all blank, any idea?

@ExcelGoogleSheets

Жыл бұрын

when you say sheet do you mean tab or file?

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.

@hariseldon791

Жыл бұрын

I found a way to create the parameter using Drilldown. It doesn't use a Table.ToList but it works.

@ExcelGoogleSheets

Жыл бұрын

Cool!

@mikeguest2763

Жыл бұрын

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.

@hariseldon791

Жыл бұрын

@@mikeguest2763 Try this video: kzread.info/dash/bejne/a5x4zatsmbHLocY.html At the 7:00 mark he reaches the part that may help you.

Why is there no queue function in Excel? It is useful when used with Google Sheet

@ExcelGoogleSheets

Жыл бұрын

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.

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

@ExcelGoogleSheets

Жыл бұрын

If I understand your question correctly, then no. You can't get separate outputs from one query.

@ExcelGoogleSheets

Жыл бұрын

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.

@jonathansmith7777

Жыл бұрын

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

@ExcelGoogleSheets

Жыл бұрын

I think the current development of M Language is Multiple Inputs -> One Output Which I agree is annoying.

@ExcelGoogleSheets

Жыл бұрын

I think JavaScript/TypeScript is much more likely to replace VBA than this.

Doesn't seem to work with numeric values, only text.

Do you know SQL . If yes , wanna learn from you

@ExcelGoogleSheets

Жыл бұрын

Noted.

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

Жыл бұрын

What is the issue?

@ClaudeBalleux

Жыл бұрын

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

Жыл бұрын

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

@ExcelGoogleSheets

Жыл бұрын

After that convert it to a list.

@ClaudeBalleux

Жыл бұрын

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

How about this is value instead of string in our query?

@ExcelGoogleSheets

Жыл бұрын

What's the difference?