Excel Text in Pivots and Power Query Magic

Подписаться 90 тыс.

Просмотров 7 тыс.

50% 1

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

Хобби

Опубликовано:

16 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 66

@rory1653 12 дней назад

This feels like magic! Talk about an excel guru!

@AccessAnalytic 12 дней назад

Cheers, thanks for the kind comment.

@karolinab9749 7 дней назад

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

@AccessAnalytic 7 дней назад

I appreciate the kind comment. Thank you.

@sledgehammer-productions 12 дней назад

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 12 дней назад

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

@zzota 12 дней назад

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

@AccessAnalytic 11 дней назад

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

@anteus64 7 дней назад

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 7 дней назад

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

@CeliaAlvesSolveExcel 11 дней назад

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

@AccessAnalytic 11 дней назад

Thank you Celia, greatly appreciated.

@rtrbs8383 12 дней назад

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

@AccessAnalytic 12 дней назад

Very kind of you to say so

@montmraaa 12 дней назад

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 12 дней назад

Thank you. Yes that's a nice touch.

@iankr 12 дней назад

Great techniques! Many thanks, Wyn.

@AccessAnalytic 11 дней назад

You’re welcome

@hubertorhant8884 12 дней назад

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

@AccessAnalytic 11 дней назад

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

@baskis69 11 дней назад

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

@AccessAnalytic 11 дней назад

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

@nebo-land 12 дней назад

Great solution!

@AccessAnalytic 12 дней назад

Thank you 😀

@ferdinandsantos6680 11 дней назад

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

@AccessAnalytic 10 дней назад

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

@BMG21 11 дней назад

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

@AccessAnalytic 11 дней назад

Very kind of you. Thanks 😄

@chrism9037 12 дней назад

Wow! Excellent Wyn

@AccessAnalytic 12 дней назад

Thanks. I appreciate the kind comment.

@RonDavidowicz 12 дней назад

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 12 дней назад

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

@GrainneDuggan_Excel 12 дней назад

Nice solution Wyn!

@AccessAnalytic 11 дней назад

Cheers Grainne

@grahamparker7729 9 дней назад

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

@AccessAnalytic 9 дней назад

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

@abhishekupadhyay6938 11 дней назад

Amazing!!! Thank you very much for sharing.

@AccessAnalytic 10 дней назад

You’re very welcome 😀

@Michael_Alaska 11 дней назад

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 11 дней назад

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

@petercompton538 6 дней назад

Another great video. Thanks very much Wyn

@AccessAnalytic 6 дней назад

Thanks so much

@PSModelling 5 дней назад

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 5 дней назад

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

@ursvenky6394 11 дней назад

Simply wow❤

@AccessAnalytic 11 дней назад

☺️ thanks

@josuezuniga5347 10 дней назад

This is beautiful, thanks!

@AccessAnalytic 10 дней назад

Thank you for the very kind comment

@RadAlzyoud 3 дня назад

Brilliant. Thanks

@AccessAnalytic 3 дня назад

You’re welcome

@ChristosKolokotronis 10 дней назад

Very interesting and useful ! Thank you!

@AccessAnalytic 10 дней назад

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

@naveenpragash7584 10 дней назад

Very useful video. Thanks!!

@AccessAnalytic 10 дней назад

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

@muhammadTayyab 11 дней назад

Just great 😁

@AccessAnalytic 11 дней назад

Thank you 🙏🏼

@MahanteshMinajagi 5 дней назад

Amazing

@AccessAnalytic 5 дней назад

Cheers

@AnbarasuAnnamalai 12 дней назад

It is a great video, Thank you

@AccessAnalytic 12 дней назад

You’re welcome, thank you

@swannous 12 дней назад

Genius ❤

@AccessAnalytic 12 дней назад

Thank you 🙏🏼

@Unknow496 5 дней назад

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 5 дней назад

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

@matthewbraun3939 11 дней назад

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

@AccessAnalytic 10 дней назад

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