Excel Text in Pivots and Power Query Magic

[@rory1653]

This feels like magic! Talk about an excel guru!

[@AccessAnalytic]

Cheers, thanks for the kind comment.

[@anteus64]

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.

[@AccessAnalytic]

Glad to help.I appreciate you taking the time to let me know you found it useful

[@karolinab9749]

Thank you for sharing!!!😊 your videos are not only Excel lessons but also maths. Very clear and logical explanation.

[@AccessAnalytic]

I appreciate the kind comment. Thank you.

[@CeliaAlvesSolveExcel]

Loved the CONCATENATEX tip, Wyn! A beautiful solution from end to end. Thank you! 🎉

[@AccessAnalytic]

Thank you Celia, greatly appreciated.

[@rtrbs8383]

I adore your quick Power Query techniques videos. Many thanks for that 💌

[@AccessAnalytic]

Very kind of you to say so

[@chrishartnell8936]

Very elegant solution. You make Excel fun!

[@AccessAnalytic]

Thank you. I appreciate the kind comment

[@BMG21]

Wow! This is fantastic, Wyn. You are a star!

[@AccessAnalytic]

Very kind of you. Thanks 😄

[@abhishekupadhyay6938]

Amazing!!! Thank you very much for sharing.

[@AccessAnalytic]

You’re very welcome 😀

[@baskis69]

Brilliant ....Excel and Power query in action¡¡¡ Thanks for sharing.

[@AccessAnalytic]

You’re welcome. Thanks for taking the time to leave a kind comment

[@westleyempeigne6541]

Love that video Wyn, especially how you integrated the parameter!

[@AccessAnalytic]

Cheers!

[@montmraaa]

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.

[@AccessAnalytic]

Thank you. Yes that's a nice touch.

[@ferdinandsantos6680]

What an awesome tutorial! Learned a lot of new things. Thank you so much!😊

[@AccessAnalytic]

I appreciate you taking the time to let me know you found it useful

[@zzota]

Very neat solution. I've never seen Integer(divide) used before.

[@AccessAnalytic]

Thanks, I’ve probably only used it a handful of times in real life.

[@hubertorhant8884]

Really stunning 🙏👍 Love the clarity of the explanation. Many thx

[@AccessAnalytic]

I appreciate you taking the time to let me know you found it useful

[@josuezuniga5347]

This is beautiful, thanks!

[@AccessAnalytic]

Thank you for the very kind comment

[@GrainneDuggan_Excel]

Nice solution Wyn!

[@AccessAnalytic]

Cheers Grainne

[@grahamparker7729]

Love it, I’m certainly going to use this.

[@AccessAnalytic]

Excellent. Thanks for taking the time to let me know.

[@sledgehammer-productions]

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.

[@AccessAnalytic]

Yes Power BI evolved out of the Excel data model / power pivot. DAX was designed with Excel users in mind. DAX & The Data Model kzbin.info/aero/PLlHDyf8d156XzYqAiOd4iLVzJM6ESiaDa Glad it’s opened up some possibilities

[@iankr]

Great techniques! Many thanks, Wyn.

[@AccessAnalytic]

You’re welcome

[@RonDavidowicz]

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.

[@AccessAnalytic]

Cheers, this was inspired by a real life scenario. Every now and again Modulo and Integer Divide come in useful 😁

[@chrism9037]

Wow! Excellent Wyn

[@AccessAnalytic]

Thanks. I appreciate the kind comment.

[@nebo-land]

Great solution!

[@AccessAnalytic]

Thank you 😀

[@ursvenky6394]

Simply wow❤

[@AccessAnalytic]

☺️ thanks

[@petercompton538]

Another great video. Thanks very much Wyn

[@AccessAnalytic]

Thanks so much

[@kennethstephani692]

Great video!!

[@AccessAnalytic]

Cheers!

[@lekonom9106]

Thx for the Measure magic.

[@AccessAnalytic]

You’re welcome.

[@ChristosKolokotronis]

Very interesting and useful ! Thank you!

[@AccessAnalytic]

You’re welcome. I appreciate you taking the time to let me know you found it useful

[@PSModelling]

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.

[@AccessAnalytic]

Thank you. Dynamic array functionality does open a huge array of options. The new PIVOTBY will take it further again 👍🏼

[@RadAlzyoud]

Brilliant. Thanks

[@AccessAnalytic]

You’re welcome

[@Michael_Alaska]

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.

[@AccessAnalytic]

Thanks. I think a groupby on Session would give session count

[@kppptt]

A great lesson! The explanation is very clear and easy to understand. In the example, which query is more important, "Attendee Data" or "ScheduleData"?

[@AccessAnalytic]

Thanks, I’m not clear on what your question is.

[@naveenpragash7584]

Very useful video. Thanks!!

[@AccessAnalytic]

I appreciate you leaving a comment to let me know. Thank you 😀

[@muhammadTayyab]

Just great 😁

[@AccessAnalytic]

Thank you 🙏🏼

[@AnbarasuAnnamalai]

It is a great video, Thank you

[@AccessAnalytic]

You’re welcome, thank you

[@swannous]

Genius ❤

[@AccessAnalytic]

Thank you 🙏🏼

[@MahanteshMinajagi]

Amazing

[@AccessAnalytic]

Cheers

[@Unknow496]

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.

[@AccessAnalytic]

I don’t quite understand sorry. What do you mean by dynamic range?

[@matthewbraun3939]

I can't remember is there a way to update the query in Power BI from a slicer?

[@AccessAnalytic]

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