Power Query made easy - learn the power of Quick Steps | Excel Off The Grid
HTML-код
- Опубликовано: 29 июл 2024
- ★ Want to automate Excel? Check out our training academy ★
exceloffthegrid.com/academy
★ About this video ★
In this video, we look at how to use a custom functions library to make Power Query easier for longer by providing ridiculously easy transformations for tricky situations.
INCLUDES:
0:00 Introduction
0:43 Example data
1:16 Power Query Quick Steps
2:50 Initial transformations
3:01 Remove null columns
3:44 Remove top rows until search value found
5:29 Flatten multiple header rows
6:37 Multiple find and replace actions in one step
7:58 Standard Power Query transformations
9:42 Update source data
10:18 Wrap-up
Get Power Query Quick Steps:
exceloffthegrid.com/academy
Learn about Power Query Quick Steps:
exceloffthegrid.com/quick-steps
★ Download 30 most useful Excel VBA Macros ebook for FREE ★
exceloffthegrid.com/
★ Where to find Excel Off The Grid ★
Blog: exceloffthegrid.com
Twitter: / exceloffthegrid
#MsExcel
Want to get your hands on Power Query Quick Steps?
▶ Get Power Query Quick Steps - exceloffthegrid.com/academy
▶ Learn about Power Query Quick Steps - exceloffthegrid.com/quick-steps
Impressive! This is a good demonstration of why a company should be ready to pay for Academy subscription!
This is just a tiny part. It's all the other parts that make the Academy subscription so worthwhile.
Great tool Mark to save doing repetitive steps.
Thanks Grainne - I appreciate that.
Thank you Mark for your always valuable video's you are providing to us ...
My pleasure!
How to add EOTG? I couldn't find it. Thank you!
It’s available in our membership academy.
Wow! The work you have put in to complete this! You have not only built an Excel Add-In but taken the time to build all those functions! And the functions are general enough to cope with the many different table types etc. Amazing!
That said, I do think Office Scripts would be my first choice in handling this. I would format both ranges as tables with the word "Breakdown" delimiting the two tables. Then, remove the null columns, null values and so on. With the Macro recorder and your preferred ChatBots' assistance most people should be able to write the script code. Where the code was not able to cope with the transformations I would probably add one or two helper tables(then can reside on other worksheets if necessary) and hide them later. Then, I would use good old Excel formulas maybe even a Lambda function., to complete the transformation.
This solution would not require the user to, even, open the workbook in desktop mode.
A Power Automate Flow could trigger the workbook update. 🤓
You know I love Office Scripts, but the issue is use-case/environment rather than the actions themselves.
If PQ comes to Excel Online, the combo of PQ, PA and OS will be incredible.
@@ExcelOffTheGrid Hi Mark, Power Query in Excel can be replaced by its online cousin - Dataflow. See my new demo video on exactly this announced on Linked in yesterday. In the case of an Excel data source the Dataflow would use that as the source, of course. In my demo, below, I use Dataverse for the demo. Can all of your functions be, easily, transferred/used in Dataflow? That is the remaining question.
See my 6 minute video: ruclips.net/video/L8pXgTV8hOE/видео.html
Les
Thanks Mark, great video
Glad you enjoyed it
Awesome👍👍
So you need a perpetual license or subscription to use this?
It's available in our membership academy. There is a minimum of 1-month subscription to gain initial access, but after that there is no requirement for on-going subscription.
Are you saying that even with just a one month subscription, and then cancelling, I will have the ability to keep using this M code assistance function? The problem with M code is that I don't know which syntax to use as there are so many of them, how will this help me?
Our goal is to help people use Excel in the right way so they can save time, stop working late and spend more time with the children, or doing what they love.
The membership has tools/downloads which help our members to do that as quickly as possible. None of those downloads have on-going fees, and can still be used if members cancel (we are not a software company, so we are not about locking people into on-going payments for tools).
Last week, we had a live masterclass session called "Understanding M Code", with Q&A afterwards. This would would have provided you all the knowledge the apply the correct M syntax. This is why our members rarely leave, because we are continually trying to add benefit.
Hi Mark - im having a horrible time figuring out how to extract the 7-digit value that starts with a “6” that is anywhere in a text string.
I can think of a few different ways to approach it. But without seeing the data it would be impossible to advise.
Its Ok you have provided these but these functions can not replace M
Given that the functions are written in M, then no, we definitely cannot replace M.
That is also why we use the term “easier for longer”, because we can never replace M.
Its just promotional video. Nothing to learn. 🙄
For a lot of Power Query users, this is exactly the tool they need to prevent months of wasted time, and to stop them giving up on Power Query.
So, in all honesty, it would be remiss of me not to tell people about it.
I, for one, learnt lots watching this video. So, I disprove your statement.
@@analysiscloud if you don't have his inbuilt function and you tried doing any of the examples practically as shown in this video and you succeeded to complete it. Then you are genius.👍..good luck for your future...
Subscription is a bad idea and money grab. I’m sure AI can create this for you. Offer a flat fee.
You do not need an on-going subscription to continue to use the tool.
Some of these could written with AI. But AI cannot give you months of user testing, performance optimization testing and live support to assess your situation and advise on the best methods to take.
Unfortunately one-time purchase doesn’t work when you provide live training, live Q&A and develop solutions specifically for the needs of the members. If I want to be continually invested in ensuring the members succeed, then a membership is the only model that works.