Excel Text in Pivots and Power Query Magic
Vložit
- čas přidán 17. 07. 2024
- ⏬Link to file
aasolutions.sharepoint.com/:f...
00:00 Intro
01:28 The Topics Covered
03:37 Index Column And GroupBy
05:30 Adding Counter inside Group
07:20 MODULO
09:47 INTEGER DIVIDE
11:54 Load to the Data Model
12:39 Insert Pivot Table
14:00 Setting up Pivot Table and adding Name
14:24 Using CONCATENATEX to display Text in Values
16:38 Adding an input Parameter avoiding formula firewall
Did you know I've written a book "Power BI for the Excel Analyst"?
pbi.guide/book/
The team and I at Access Analytic develop Power BI and Excel solutions for clients in Australia and deliver training around the World. accessanalytic.com.au/
Connect with me
wyn.bio.link/ - Jak na to + styl
This feels like magic! Talk about an excel guru!
Cheers, thanks for the kind comment.
Thank you for sharing!!!😊 your videos are not only Excel lessons but also maths. Very clear and logical explanation.
I appreciate the kind comment. Thank you.
wow, this is extremely useful. I always had firewall issues because of the (as just learnt) navigation element in the parameters - thank you for solving this.
Glad to help.I appreciate you taking the time to let me know you found it useful
First time seeing a sensible use case for an index starting at 0.
Also realising that the datamodel in a pivot table kind of looks like things you can do in Power BI (Power BI equals (to me) Dax, and probably the measures too). Mind blown.
Yes Power BI evolved out of the Excel data model / power pivot. DAX was designed with Excel users in mind. DAX & The Data Model
czcams.com/play/PLlHDyf8d156XzYqAiOd4iLVzJM6ESiaDa.html
Glad it’s opened up some possibilities
Loved the CONCATENATEX tip, Wyn! A beautiful solution from end to end. Thank you! 🎉
Thank you Celia, greatly appreciated.
I adore your quick Power Query techniques videos. Many thanks for that 💌
Very kind of you to say so
Very neat solution. I've never seen Integer(divide) used before.
Thanks, I’ve probably only used it a handful of times in real life.
This is excellent. I believe that adding a macro that executes to refresh the pivot table whenever the max capacity value changes, making the update dynamic, would be a great final addition.
Thank you. Yes that's a nice touch.
What an awesome tutorial! Learned a lot of new things. Thank you so much!😊
I appreciate you taking the time to let me know you found it useful
Great techniques! Many thanks, Wyn.
You’re welcome
Really stunning 🙏👍
Love the clarity of the explanation.
Many thx
I appreciate you taking the time to let me know you found it useful
Wow! This is fantastic, Wyn. You are a star!
Very kind of you. Thanks 😄
Brilliant ....Excel and Power query in action¡¡¡ Thanks for sharing.
You’re welcome. Thanks for taking the time to leave a kind comment
Great solution!
Thank you 😀
Wow! Excellent Wyn
Thanks. I appreciate the kind comment.
Nice solution Wyn!
Cheers Grainne
Love it, I’m certainly going to use this.
Excellent. Thanks for taking the time to let me know.
Great video, not sure how I’d make use of these concepts, but I’ll keep them in the back of my mind. Thank you.
Cheers, this was inspired by a real life scenario. Every now and again Modulo and Integer Divide come in useful 😁
Amazing!!! Thank you very much for sharing.
You’re very welcome 😀
Another great video. Thanks very much Wyn
Thanks so much
Simply wow❤
☺️ thanks
This is beautiful, thanks!
Thank you for the very kind comment
Brilliant. Thanks
You’re welcome
Very interesting and useful ! Thank you!
You’re welcome. I appreciate you taking the time to let me know you found it useful
That is fantastic! Considering the example, it would be useful if there was some way to balance the count among the sessions. For example, for a max class size of 6 with 7 students signed up it spread them out as 4-3 instead of 6-1.
Thanks. I think a groupby on Session would give session count
Very nice, would have probably used a formula approach to try to solve this so interesting to see it done in a more robust way in Power Query.
Thank you. Dynamic array functionality does open a huge array of options. The new PIVOTBY will take it further again 👍🏼
Very useful video. Thanks!!
I appreciate you leaving a comment to let me know. Thank you 😀
Just great
😁
Thank you 🙏🏼
Amazing
Cheers
It is a great video, Thank you
You’re welcome, thank you
Genius ❤
Thank you 🙏🏼
Is it possible to use multiple values for the dynamic range?
I normally use a difference solution for the dynamic values and to update sql queries (date range), (product), (value range for filters), etc. I would love to see a video explaining more about it and more examples.
I don’t quite understand sorry. What do you mean by dynamic range?
I can't remember is there a way to update the query in Power BI from a slicer?
Matthew, as far as I know only a direct query can utilises a slicer learn.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters
In power bi desktop you can set up a power query parameter which can then be input manually on each refresh