Power Query Tutorial: Build an Interactive Form in Excel
Вставка
- Опубліковано 12 вер 2024
- Do you want to add some interactivity to your Excel? You send the Excel file to someone and they can input some cell and have some output dynamically change. Plus do this without writing any VBA! We'll unlock the power of Power Query to build an interactive form in Excel. Perfect for both beginners and advanced users, this guide will teach you step-by-step how to create forms that can dynamically interact with your data. By the end of this video, you’ll not only boost your Excel skills but also streamline data entry and analysis. Get ready to transform how you manage data with our expert tips and tricks on creating a sophisticated interactive form using Power Query.
Though it does involves editing some of the M code (the language Power Query uses), it's not tooooo much. This can also be known as a parameter query because you are passing (some) user defined parameter to the query for it to do some work. This involves creating two queries in Power Query - (1) a function query and then (2) a plain query from a form (basically a table) where someone enters information. See the video to check out the steps.
If you found this video helpful, please give us a thumbs up and consider subscribing for more Excel tutorials. Got questions or need further clarification? Leave your thoughts in the comments below.
🔔 SUBSCRIBE to my channel ➜ goo.gl/wN3c3p
🏫 Excel Training ➜ www.exceltrain...
📚 Excel Books & Tech Gear ➜ www.amazon.com...
⚙️ Tools: Screencasting ➜ techsmith.z6rj...
⚙️ Tools: Microsoft Office ➜ microsoft.msaf...
⚙️ Tools: TubeBuddy ➜ www.tubebuddy....
📝 This description may contain affiliate links and we'll receive a small commission if a purchased is made using the links (but at no additional cost to you). It'll support the channel and so more videos like this can be made. Thanks for your support!
🎁 If you find these videos useful and want to support my channel go to www.buymeacoff...
#excel
#msexcel
#doughexcel
~-~~-~~~-~~-~
Please watch: "Convert Table in a PDF File to Excel"
• Convert Table in a PDF...
~-~~-~~~-~~-~
Hi Doug, just wanted to take a minute and thank you for this video. It's the second time I use these instructions, and they are the most straight forward and to the point. It's an elegant solution. Thank you.
Hi blackibanez, wow...thanks for the kind words!
Thank you for this! Now I have a better sense of parameters and PQ Custom functions. You make it really easy to understand.
Hi Excel On Fire, glad you liked it, thanks for commenting!
Thank you, Doug. Very clear and helpful.
Hi Allen Baranov, glad you liked it, thanks for commenting!
Thanks!
Thanks Mike!
Thank you very much! Really powerful knowledge!
Hi Héctor Noguer Bonetti, thanks for the comment!
Thank for this wonderful video.
Hi Savio D'Cunha, thanks for the comment!
very useful!
Hi Steven Nye, glad you liked it, thanks for commenting!
Awesome! Thanks!
You're Welcome!
thank you!
You're Welcome!
Thank you so much!!!
You're Welcome!
Thank you! nice video
Hi Rosa Amelia...you're welcome, glad you liked!
Hi, thanks, great video... i need to do something very simular only i have my data in a SQL table - i can import it but would like to use a variable to show exact results. The variable would be based on a column name within the table... any ideas on how i can do this or point me in a direction please? thansk again
Hi Daniel Ward, .. try a post on the mrexcel.com forum!
Hi Doug. I'm trying to pass a SQL query into a Power Query; not just a parameter but the whole script. It's actually a multi-line update script followed by a "Select now() as Last_Update". I am able to run/refresh it manually so I know it works. It continues to work when I convert the original query into a function and then invoke it in the new query. Where it fails is when I replace the SQL scripts with "sqlUpdates"; which show no syntax errors at the bottom, so I load and save.
Update_Function=(sqlUpdate)=>
let
Source = Odbc.Query("dsn=global_fah", "&sqlUpdate&")
in
Source
in Update_Function
This is the table2 M code, which also shows no syntax errors:
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"sql_Code", type text}}),
#"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "Query1", each Query1([sql_Code])),
Query2 = #"Invoked Custom Function"{0}[Query1]
in
Query2
But then I get this error:
DataSource.Error: ODBC: ERROR [42000] [PSQL][ODBC Client Interface][LNA][PSQL][SQL Engine]Syntax Error: >&sqlUpdate&
Details:
DataSourceKind=Odbc
DataSourcePath=dsn=global_fah
OdbcErrors=Table
Can you help me out please??
Never-mind. I solved the problem.
Hi Mike Myers, want to add your solution to the thread so others can get insight?
Hey Doug - is there a playlist?
Thanks
here ya go
ua-cam.com/play/PL-n8f1cY_Qw9v3Y-jSrPy8-ctFw2yJjLZ.html
Thanks
You're Welcome!
is there an easy way to have TWO inputs for this example, as opposed to just a year input...like a year and a month input?
for year and month , you can have the inputs as separate cells, concatenate them and pass that along to PQ. For example January 2018 can be 201801. Just make it consistent on the lookup side with concatenation.
good call...that worked great!
Very helpful and solved my problem. However, if I try to duplicate existing worksheet1 to worksheet2 in the same workbook and set worksheet1 year to 2017 and worksheet2 year to 2018, then refresh it, I have 2 problems. 1st problem is both worksheet2 will have same query output as worksheet1 and 2nd problem is in worksheet2, the query will insert the queried data into same cells and pushing existing old data to the right side. Perhaps for 1st problem, both queries are using the same year in worksheet1. How can I solve this problem?
you might need to check back into the query editor to view the source of where it is pulling and then when it exports back to excel where it is exporting to. For initial thoughts, it sounds like an option to bring this in as one table and then use the multiple reports feature of the Pivot Table
ua-cam.com/video/zmdyc4Vf7DU/v-deo.html
So I know this is an older Video, but I'm curious if you can use this same method to look for a specific file in a folder? Your Source is a Web page and I can see that essentially you are just tell Power Query to look at a table on a specific web address. So do i have to convert my source into tables, before I try tell it to look for the File Name?
Kinda related to your question...see if this video provides some insight ua-cam.com/video/F1jMeWA4Law/v-deo.html
is there any way to disable the jumping from table to table when i refresh the query as i did this on 3 tables but dont want the refresh to keep jumping over
if refreshing all i think it'll do all the queries; but you can refresh just that one query
In my excel, I can't find the invoke button at query editor. Where to active it? Excel 2016 version.
might check on your version capabilities on the msft website
Super complicado es ahora crear parámetros!!!!!!!
Gracias por el comentario