Blog

Bonbon's Guide To Data & Tech > Blog

GROUPBY & PIVOTBY Functions to Create a 1/2 Year Summary Report! Excel Magic Trick 1863

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1863.xlsx
Learn how to use the amazing GROUPBY and PIVOTBY functions to create a sales report with categories to summaries by half year increments.
Topics:
1. (00:00) Introduction
2. (00:02) Viewer Suggestion
3. (00:18) IF Function to create ½ data attributes for the table of data.
4. (00:43) GROUPBY Function
5. (01:47) Conditional Formatting for Report
6. (02:22) GO TO Trick to find cells in worksheet that contain Conditional Formatting
7. (02:48) Mixed Cell References and Conditional Formatting for Total Row
8. (04:26) PIVOTBY Function
9. (05:04) Add New Data and Watch Formulas Update!!!
10. (05:37) Summary
11. (05:50) Closing, Video Links

#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics Continue reading “GROUPBY & PIVOTBY Functions to Create a 1/2 Year Summary Report! Excel Magic Trick 1863” →

Special Offer: New Power BI & Excel Power Query M Code Book from excelisfun!!!

Posted on by Annie D

Here is link: https://www.mrexcel.com/products/the-transformative-magic-of-power-query-m-code-in-excel-and-power-bi/ or Amazon: https://www.amazon.com/Transformative-Magic-Power-Query-Excel/dp/1615470832/ref=sr_1_1
Mike “excelisfun” Girvin’s 4th book: The Transformative Magic of Power Query M Code in Power BI & Excel.

Book about Power Query M Code to help data analysts transform and shape data into useful, actionable information for decision makers.

Power Query VS. Excel Dates: PQ Solves the Famous 2/29/1900 Date Error So Eloquently! EMT 1864

Posted on by Annie D

Here is link: https://www.mrexcel.com/products/the-transformative-magic-of-power-query-m-code-in-excel-and-power-bi/ or Amazon: https://www.amazon.com/Transformative-Magic-Power-Query-Excel/dp/1615470832/ref=sr_1_1
Mike “excelisfun” Girvin’s 4th book: The Transformative Magic of Power Query M Code in Power BI & Excel.

Book about Power Query M Code to help data analysts transform and shape data into useful, actionable information for decision makers.

Remember:
Microsoft and Lotus though that 1900 was a leap year and so they added 2/29/1900 as a date in Excel. But if you import your dates into Excel or the reverse, all dates work.

Approximate Match Lookup in Power Query: 2 Amazing Functions! Power BI or Excel. EMT 1865

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1865.xlsx
Learn how to perform approximate match lookup in Power Query M Code by creating custom functions. The first function we build in a Custom Column using the Table.SelectRows function and the let expression. The second we build as a reusable function in the Advanced Editor using the Table.SelectRows function. The third we build as a reusable function in the Advanced Editor using the List.Accumulate function.

M Code book from excelisfun at Mr Excel Site: https://www.mrexcel.com/products/the-transformative-magic-of-power-query-m-code-in-excel-and-power-bi/

M Code book from excelisfun at Amazon: https://www.amazon.com/Transformative-Magic-Power-Query-Excel/dp/1615470832/ref=sr_1_1

The M Code book is Mike “excelisfun” Girvin’s Continue reading “Approximate Match Lookup in Power Query: 2 Amazing Functions! Power BI or Excel. EMT 1865” →

Expert Guide: GitHub Integration for Microsoft Fabric

Posted on by Annie D

We talked about Git integration with Azure DevOps in the past, but now you can integrate GitHub with Microsoft Fabric! Once things are configured most things are the same because it’s just Git! Adam walks you through it!

Introduction to Git integration (preview)
https://learn.microsoft.com/fabric/cicd/git-integration/intro-to-git-integration?tabs=github

Basic concepts in Git integration
https://learn.microsoft.com/fabric/cicd/git-integration/git-integration-process?tabs=GitHub%2Cazure-devops

📢 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
— <a Continue reading “Expert Guide: GitHub Integration for Microsoft Fabric” →

The Surprising Enhancements for DAX Query View in Power BI

Posted on by Annie D

The DAX Query View for Power BI got some nice updates and a special item for those on a Mac! Zoe Douglas walks us through what we can do!

Zoe Douglas
https://www.linkedin.com/in/zoedouglas-data/
https://x.com/datazoepowerbi

📢 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 Continue reading “The Surprising Enhancements for DAX Query View in Power BI” →

Unleashing Data for OneLake with Mirroring in Microsoft Fabric

Posted on by Annie D

Learn how to replicate your database to Microsoft Fabric using Mirroring! Patrick walks you through how you can do this with Azure SQL Database and CosmosDB and join across the two.

What is Mirroring in Fabric?
https://learn.microsoft.com/fabric/database/mirrored-database/overview

Mirroring Azure SQL Database (Preview)
https://learn.microsoft.com/fabric/database/mirrored-database/azure-sql-database

Mirroring Azure Cosmos DB (Preview)
https://learn.microsoft.com/fabric/database/mirrored-database/azure-cosmos-db

📢 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
— <a Continue reading “Unleashing Data for OneLake with Mirroring in Microsoft Fabric” →

Design Engaging Power BI Report Page Tooltips Effortlessly

Posted on by Annie D

We’ve talked about Tooltips before, but this time Adam walks you through how to recreate his Report Page Tooltip in Power BI Desktop. Learn how you can make things a bit more exciting and engaging!

Create tooltips based on report pages
https://learn.microsoft.com/power-bi/create-reports/desktop-tooltips?tabs=powerbi-desktop

📢 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
— <a href="https://www.instagram.com/guyinacube/" Continue reading “Design Engaging Power BI Report Page Tooltips Effortlessly” →

Some GOTCHAs when using Mirroring in Microsoft Fabric

Posted on by Annie D

Mirroring in Microsoft Fabric can be very powerful but setting them up can sometimes have some frustrations. Patrick walks you through the challenges he hit and how he got around it.

What is Mirroring in Fabric?
https://learn.microsoft.com/fabric/database/mirrored-database/overview

Mirroring Azure SQL Database (Preview)
https://learn.microsoft.com/fabric/database/mirrored-database/azure-sql-database

Mirroring Azure Cosmos DB (Preview)
https://learn.microsoft.com/fabric/database/mirrored-database/azure-cosmos-db

📢 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
— <a Continue reading “Some GOTCHAs when using Mirroring in Microsoft Fabric” →

Excel Why Are SEC Edgar Numbers Showing as Text in Excel? – Episode 2654

Posted on by Annie D

Microsoft Excel Tutorial: Why Are SEC Edgar Numbers Showing as Text in Excel?

In this episode of the Learn Excel podcast, MrExcel dives into a common issue when working with numbers copied from the SEC Edgar database. Sometimes, Excel displays these numbers as text, causing frustrating #VALUE! errors during calculations. We’ll take a quick break from Copilot and explore why this happens and how to fix it!

The problem? Non-breaking spaces (character 160) sneak into your data when copying from sources like SEC Edgar. Even though they look like regular spaces, they can cause Excel to treat numeric data as text. Continue reading “Excel Why Are SEC Edgar Numbers Showing as Text in Excel? – Episode 2654” →

Copilot In OneDrive Versus Copilot In Excel for Excel Workbooks – Episode 2655

Posted on by Annie D

Microsoft Excel Tutorial: Copilot In OneDrive Versus Copilot In Excel for Excel Workbooks

In today’s video, we’re diving into Copilot in OneDrive and how it handles analyzing an Excel workbook.

But first, let’s settle a little controversy that popped up about my “Spreadsheets are my Jam” coffee mugs. Take my survey to let me know if you carry your coffee mug in your dominant hand or not.

Now, let’s get back to Excel. Remember that awesome trick from Dr. Nitin, where he used Copilot in Word to analyze financial statements? Well, I decided to throw a regular Excel file at Copilot Continue reading “Copilot In OneDrive Versus Copilot In Excel for Excel Workbooks – Episode 2655” →

Excel Word Cloud Using Copilot And Python In Excel – Episode 2656

Posted on by Annie D

Microsoft Excel Tutorial: Word Cloud Using Copilot And Python In Excel – Episode 2656

Creating a word cloud in Microsoft Excel has never been easier! In this video, I walk you through how to generate a word cloud using the new Copilot – Advanced Analysis feature. With just a few clicks, I transformed 3000 rows of data from the Title column into a visually stunning word cloud. The best part? Excel automatically excludes common stop words like “The,” “A,” and “of” so you can focus on the key terms.

I was amazed at how Excel leverages Python to make this happen behind Continue reading “Excel Word Cloud Using Copilot And Python In Excel – Episode 2656” →