Category: Power Query

Excel Plot A Bell Curve in Excel – Episode 2596

Posted on by Annie D

Microsoft 365 Excel Tutorial – Plot a Bell Curve with 2 Formulas in Excel.

Download the finished spreadsheet from: https://www.mrexcel.com/youtube/QW3tbhe6Gks/

If you don’t have the SEQUENCE function, use this older video: https://www.youtube.com/watch?v=_PqnDYMO3lw

Search terms:
making a bell curve in excel
making a bell curve in excel from data
how excellent is thy name by lecreia campbell
how to make a bell curve in excel with data
how to find the shaded area of bell curve excel
how to make excel histogram a bell
how to create a bell curve in excel
how to make a bell curve in excel
how to create bell curve in excel
how Continue reading “Excel Plot A Bell Curve in Excel – Episode 2596” →

Excel Stacked And Clustered Chart in Excel – Epsiode 2595

Posted on by Annie D

Microsoft Excel Tutorial – Creating Cluster Stack Chart in Excel.

Jeff from Akron asks how to create a column chart where two series are stacked, but a third series is clustered. Episode 2595 shows you how.

To download the workbook: https://www.mrexcel.com/youtube/c7dPCe2AIEQ/

This video requires Excel 2013 or newer. There is an older video for Excel 2007 or Excel 2010: https://youtu.be/j8kMp5IC6oE

For other variations:
Leila has a video where each column has a Total (say 2000) and a second column showing a subset (say 500). That video is here: https://youtu.be/IwWh3UOTymE

Jon Peltier sells a charting utility where there Continue reading “Excel Stacked And Clustered Chart in Excel – Epsiode 2595” →

Excel VLOOKUP To Return Many Columns Now Easier With XLOOKUP – Episode 2594

Posted on by Annie D

Microsoft Excel Tutorial: Returning many columns from a lookup.
If you do not have the XLOOKUP function, then use the techniques from episode 1123: https://www.youtube.com/watch?v=T_L2XAC0kq8

In the past, if you needed to return 4 quarters or 12 months or 52 weeks from a VLOOKUP, it required some trickery or helper cells. Today, the new XLOOKUP function makes it simple to return many columns from a lookup in Excel.

How to apply vlookup in excel to get multiple columns values.
How to return 12 months from a VLOOKUP in Excel.
How to return multiple columns from a VLOOKUP in Excel.
How to return 52 Continue reading “Excel VLOOKUP To Return Many Columns Now Easier With XLOOKUP – Episode 2594” →

Excel Cell Deleted But Not Logged To Show Changes – Epsiode 2593

Posted on by Annie D

Microsoft Excel Tutorial

Someone deleted some cells in Excel Online.
Using the Version History, it happened between 11:01 and 11:07 AM.
But Show Changes is not showing who changed it?

How could this be happening?
One way is to overwhelm Show Changes by making 3000 changes with VBA.
Another way might be changes by Power Automate Flows.
Do you have another way?

Excel Searching During File Open 2592

Posted on by Annie D

Microsoft Excel Tutorial.
When you go to File, Open, there is a Search box at the top. This search box often surprises me by offering files that are years old. I always wondered how it is working. My friends on the Excel team pointed me to Theo Lorrain-Hale, a Project Manager who worked on the search box.
In this episode, I share tips for where the search box is actually searching.

Excel Add A Calculation To Each Pivot Table Subtotal Row Epsiode 2591

Posted on by Annie D

Microsoft Excel tutorial on pivot tables.
Todd wants to calculate (12/52)*Sales on each subtotal row in a pivot table.
Bill shows how to add a calculated field to a pivot table and then uses some formatting to show the field only on the subtotal rows.

To download the data: https://www.mrexcel.com/youtube/-WqKrzOyCN8/

Table of Contents
(0:00) Formula outside of pivot table
(0:28) Calculated Field in Pivot Table
(1:11) Verify the calculation
(1:30) White Font trick
(1:55) When pivot table changes
(2:35) Wrap-up

ChatGPT blew my mind with this Power BI scenario

Posted on by Annie D

Our minds were blown by this ChatGPT example for Power BI from our friends Stephanie Bruno and Shannon Lindsay! Use ChatGPT to help get you pointed in the right direction and with a faster solution.

Power BI datasets CI/CD (the easy-ish way)
https://data-witches.com/2023/03/31/power-bi-datasets-ci-cd-the-easy-ish-way/

Stephanie Bruno
https://twitter.com/StephTBruno
https://www.linkedin.com/in/brunostephanie/
https://data-witches.com

Shannon Lindsay
https://twitter.com/shan_gsd
https://www.linkedin.com/in/shannonrlindsay/
https://data-witches.com

📢 Become a member: https://guyinacu.be/membership

*******************

Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.

🎓 Guy in a Cube courses: <a href="https://guyinacu.be/courses" Continue reading “ChatGPT blew my mind with this Power BI scenario” →

Excel – Best Way For Running Totals – Episode 2590

Posted on by Annie D

Microsoft Excel how-to video about Running Totals in Excel.
In one of my Bing Shorts, I showed two different ways to do Running Totals in Excel.
And then, an interesting comment from Carlo in Italy with a running total formula that I’ve never seen before.

To download the data from today, go here: https://www.mrexcel.com/youtube/TnkyqfGGV9g/

I had to bring that formula to Excel to visualize how it was working. It was cool that it used a colon next to INDEX. But is there a simpler way?

In the video, I then compare four different Running Total Formulas. Which are easiest to enter? Which Continue reading “Excel – Best Way For Running Totals – Episode 2590” →

3 tips for the new On-Object editing of a Power BI visual

Posted on by Annie D

Have you started using the new on-object editing for Power BI visuals? Adam found some hidden gems that you may not be aware of when using it! You need to be aware of these!

Use on-object interaction with visuals in your report (preview)
https://learn.microsoft.com/power-bi/create-reports/power-bi-on-object-interaction

📢 Become a member: https://guyinacu.be/membership

*******************

Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.

🎓 Guy in a Cube courses: https://guyinacu.be/courses

*******************
LET’S CONNECT!
*******************

— http://twitter.com/guyinacube
— http://twitter.com/awsaxton
— http://twitter.com/patrickdba
— http://www.facebook.com/guyinacube
— Continue reading “3 tips for the new On-Object editing of a Power BI visual” →

Loop through a list using pySpark for your Azure Synapse Pipelines

Posted on by Annie D

Curious how to loop through files using pySpark? Patrick walks through how he did it for use within his Azure Synapse Analytics Pipelines and Notebooks.

📢 Become a member: https://guyinacu.be/membership

*******************

Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.

🎓 Guy in a Cube courses: https://guyinacu.be/courses

*******************
LET’S CONNECT!
*******************

— http://twitter.com/guyinacube
— http://twitter.com/awsaxton
— http://twitter.com/patrickdba
— http://www.facebook.com/guyinacube
— https://www.instagram.com/guyinacube/
— https://guyinacube.com

***Gear***
🛠 Check out my Tools page – https://guyinacube.com/tools/

#AzureSynapse #pySpark #GuyInACube

Excel Count By Week – Episode 2589

Posted on by Annie D

Microsoft Excel tutorial on how to count records by week.
This video includes three methods:
Using Group Field in a pivot table to roll up to weeks.
Using a formula of Data minus WEEKNUM(,3) to back the date to Monday.
Using ISOWEEKNUM plus the year along with SORT, UNIQUE, and COUNTIF.

To download the data, visit: https://www.mrexcel.com/youtube/GLkEsvirk9Y/

Table of Contents
(0:00) Count by Week in Excel pivot table
(0:43) Pivot convert dates to weeks
(1:33) Using date minus WEEKDAY(,3)
(2:19) Sort, Unique, COUNTIF
(2:53) Using ISOWEEKNUM in Excel
(3:30) Excel formula for Year and Week Number

🔴 Power BI tips from the Pros – LIVE (April 15, 2023) (Member Chat 2nd Half)

Posted on by Annie D

Join us live as we answer your questions about Power BI!

💥 30 minutes: Open Q&A (Public)
💥 Then… Members Only Chat (Public can watch & Super Chat for Questions)

📢 RULES FOR Q&A: 📢

👉 Put a “Q:” in front of your comment to help us identify questions!
👉 Super chats take priority
👉 Do NOT re-post your questions! We’ll get to as many questions as we can.
👉 If you SPAM your question, you will be put in timeout

📢 Become a member: https://guyinacu.be/membership

*******************

Want to take your Power BI skills to the next level? We have training courses available to help you Continue reading “🔴 Power BI tips from the Pros – LIVE (April 15, 2023) (Member Chat 2nd Half)” →