Excel Text in Pivots and Power Query Magic

Рет қаралды 7,549

Күн бұрын

⏬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/

Пікірлер: 66

@rory1653 16 күн бұрын

This feels like magic! Talk about an excel guru!

@AccessAnalytic 16 күн бұрын

Cheers, thanks for the kind comment.

@sledgehammer-productions 16 күн бұрын

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 16 күн бұрын

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

@CeliaAlvesSolveExcel 16 күн бұрын

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

@AccessAnalytic 16 күн бұрын

Thank you Celia, greatly appreciated.

@rtrbs8383 16 күн бұрын

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

@AccessAnalytic 16 күн бұрын

Very kind of you to say so

@montmraaa 17 күн бұрын

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 17 күн бұрын

Thank you. Yes that's a nice touch.

@karolinab9749 12 күн бұрын

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

@AccessAnalytic 12 күн бұрын

I appreciate the kind comment. Thank you.

@zzota 16 күн бұрын

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

@AccessAnalytic 16 күн бұрын

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

@anteus64 12 күн бұрын

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 12 күн бұрын

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

@hubertorhant8884 16 күн бұрын

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

@AccessAnalytic 16 күн бұрын

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

@baskis69 15 күн бұрын

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

@AccessAnalytic 15 күн бұрын

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

@iankr 16 күн бұрын

Great techniques! Many thanks, Wyn.

@AccessAnalytic 16 күн бұрын

You’re welcome

@BMG21 15 күн бұрын

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

@AccessAnalytic 15 күн бұрын

Very kind of you. Thanks 😄

@ferdinandsantos6680 15 күн бұрын

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

@AccessAnalytic 15 күн бұрын

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

@nebo-land 16 күн бұрын

Great solution!

@AccessAnalytic 16 күн бұрын

Thank you 😀

@chrism9037 16 күн бұрын

Wow! Excellent Wyn

@AccessAnalytic 16 күн бұрын

Thanks. I appreciate the kind comment.

@RonDavidowicz 17 күн бұрын

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 17 күн бұрын

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

@GrainneDuggan_Excel 16 күн бұрын

Nice solution Wyn!

@AccessAnalytic 16 күн бұрын

Cheers Grainne

@abhishekupadhyay6938 15 күн бұрын

Amazing!!! Thank you very much for sharing.

@AccessAnalytic 15 күн бұрын

You’re very welcome 😀

@grahamparker7729 13 күн бұрын

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

@AccessAnalytic 13 күн бұрын

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

@josuezuniga5347 15 күн бұрын

This is beautiful, thanks!

@AccessAnalytic 15 күн бұрын

Thank you for the very kind comment

@petercompton538 11 күн бұрын

Another great video. Thanks very much Wyn

@AccessAnalytic 10 күн бұрын

Thanks so much

@Michael_Alaska 16 күн бұрын

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 16 күн бұрын

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

@ursvenky6394 16 күн бұрын

Simply wow❤

@AccessAnalytic 16 күн бұрын

☺️ thanks

@ChristosKolokotronis 15 күн бұрын

Very interesting and useful ! Thank you!

@AccessAnalytic 15 күн бұрын

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

@RadAlzyoud 8 күн бұрын

Brilliant. Thanks

@AccessAnalytic 8 күн бұрын

You’re welcome

@naveenpragash7584 14 күн бұрын

Very useful video. Thanks!!

@AccessAnalytic 14 күн бұрын

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

@PSModelling 10 күн бұрын

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 10 күн бұрын

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

@AnbarasuAnnamalai 16 күн бұрын

It is a great video, Thank you

@AccessAnalytic 16 күн бұрын

You’re welcome, thank you

@muhammadTayyab 16 күн бұрын

Just great 😁

@AccessAnalytic 16 күн бұрын

Thank you 🙏🏼

@MahanteshMinajagi 9 күн бұрын

Amazing

@AccessAnalytic 9 күн бұрын

Cheers

@swannous 16 күн бұрын

Genius ❤

@AccessAnalytic 16 күн бұрын

Thank you 🙏🏼

@Unknow496 10 күн бұрын

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 9 күн бұрын

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

@matthewbraun3939 15 күн бұрын

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

@AccessAnalytic 15 күн бұрын

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