Blog

Bonbon's Guide To Data & Tech > Blog

Modern Analytics Freestyle

Posted on by Annie D

Join us for an enlightening virtual event that delves into the transformative power of modern data analysis. Discover how today’s data analysts, equipped with the latest AI tools, can significantly enhance and streamline your workflow. As we collaboratively navigate a real-world data challenge, we’ll unveil a plethora of innovative data analysis techniques that promise to redefine the boundaries of what you believed was possible. Elevate your analytical prowess and extend your capabilities beyond what you thought achievable just a year ago. Don’t miss this opportunity to be at the forefront of the data revolution!

This event will take place on Enterprise Continue reading “Modern Analytics Freestyle” →

Lookup Multiplying For Commission Calculation: FILTER, XLOOKUP, LAMBDA & BYROW. EMT 1838.

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1838.xlsx
Learn about how to calculate commission for each sales rep who sell multiple products that each have a different commission rate. This is a complicated Lookup Multiplying calculation that is made easy with Microsoft 365 Excel.
Topics:
1. (00:00) Introduction
2. (00:26) Commission Rate Data Table Setup
3. (00:53) Old School Formulas and manual Method
4. (01:09) FILTER, XLOOKUP and SUM Method
5. (03:04) LAMBDA and BYROW Method
6. (04:42) Summary
7. (05:04) Closing, Video Links

#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #lookup #xlookup #vlookup #vlookupformula #commission

Excel Sum Across Sheets When Rows Do Not Line Up – Episode 2622

Posted on by Annie D

Microsoft Excel Tutorial: Sum across worksheets when rows do not line up?

Today’s question from Rebaz on podcast 1984 – Excel Sum Across Worksheets: “if we have different cell in different sheet, how can I Sum?”

Download the workbook from today: https://www.mrexcel.com/youtube/UO11Ase1_Ys/

This video shows you an easy way to build a 3-D reference in Excel, also known as a spearing formula. Excel functions include SUM, XLOOKUP, FILTER, SUMIFS, TEXTJOIN, TEXTSPLIT, SUMPRODUCT, Helper Arrays, LET, Python in Excel, and VSTACK.

Table of Contents
(0:00) Problem: Adding Across Sheets that are not lined up
(0:43) How to build a 3-D Reference
(1:19) Does XLOOKUP work Continue reading “Excel Sum Across Sheets When Rows Do Not Line Up – Episode 2622” →

No more Report Builder for Power BI Paginated Reports???

Posted on by Annie D

Not a fan of Power BI Report Builder? Still want to use Paginated reports? Patrick shows the new Paginated authoring experience within the service! It’s BANANAS!

Low code authoring of paginated reports
https://powerbi.microsoft.com/blog/low-code-authoring-of-paginated-reports/

Create paginated reports that you can export in the Power BI service
https://learn.microsoft.com/power-bi/paginated-reports/web-authoring/paginated-formatted-table

📢 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
— <a Continue reading “No more Report Builder for Power BI Paginated Reports???” →

Landing data with Dataflows Gen2 in Microsoft Fabric

Posted on by Annie D

Pipelines are cool in Microsoft Fabric, but how could we use Dataflows to get data into our Data Warehouse? Patrick shows another way to move your data with just a few clicks!

📢 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 Continue reading “Landing data with Dataflows Gen2 in Microsoft Fabric” →

Creating your first Data Warehouse in Microsoft Fabric

Posted on by Annie D

It’s time to show the Data Warehouse within Microsoft Fabric some love! Patrick walks you through how you can get started with your first Data Warehouse using Data Pipelines.

What is data warehousing in Microsoft Fabric?
https://learn.microsoft.com/fabric/data-warehouse/data-warehousing

Microsoft Fabric decision guide: data warehouse or lakehouse
https://learn.microsoft.com/fabric/get-started/decision-guide-warehouse-lakehouse?toc=%2Ffabric%2Fdata-warehouse%2Ftoc.json&bc=%2Ffabric%2Fdata-warehouse%2Ftoc.json

Data warehouse tutorial introduction
https://learn.microsoft.com/fabric/data-warehouse/tutorial-introduction

Ingest data into the Warehouse
https://learn.microsoft.com/fabric/data-warehouse/ingest-data

📢 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!
*******************

— Continue reading “Creating your first Data Warehouse in Microsoft Fabric” →

FILTER Function with List of Partial Text Contains Criteria & Not Contains Criteria. EMT 1837

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1835-1837.xlsx
Learn about how to use a list of partial text contains criteria to filter a list using the FILTER Function. See Contains and Does Not Contains Criteria. See three methods including the easiest version that uses the BYROWS function and LAMBDA Function rather than MMULT function and matrix algebra..
Topics:
1. (00:00) Introduction
2. (00:33) SEARCH Function
3. (01:20) TOROW Function
4. (20:20) 2023 formula
5. (02:33) 2020 or later formula
6. (02:46) 2010 or later formula
7. (02:53) ISNUMBER function and Double Negative
8. (03:09) BYROWS and LAMBDA Functions
9. (04:32) FILTER Function
10. (05:01) Filter by contains
11. (05:10) Filter by Not Contains using Continue reading “FILTER Function with List of Partial Text Contains Criteria & Not Contains Criteria. EMT 1837” →

Excel Python Appending Data Frames From Multiple Worksheets – Episode 2621

Posted on by Annie D

Microsoft Excel Tutorial: Appending data from multiple worksheets using Python in Excel

To download today’s workbook: https://www.mrexcel.com/youtube/3rAJPcjO6Ok/

Today, a question about creating a Python data frame from multiple Excel sheets. I use the CONCAT function in Python but then realize that the headings are repeated.
So I show how to use .tail(-1) to remove the top row from each data frame except the first.

Table of Contents
(0:00) Problem Statement
(0:29) Defining 3 data frames
(1:32) Python CONCAT function
(2:20) Python Tail Function
(3:10) Wrap-up

A quirky Bookmark scenario with Filters in Power BI. Pay Attention!

Posted on by Annie D

A question came up about why bookmarks weren’t working and how to solve a particular scenario regarding filters in Power BI. Adam shows how to solve this and uses a little DAX to do it!

📢 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 Continue reading “A quirky Bookmark scenario with Filters in Power BI. Pay Attention!” →

Excel Python 3D Scatterplot – Episode 2620

Posted on by Annie D

Microsoft Excel Tutorial – Drawing a 3D Scatterplot using Python in Excel

Download the workbook: https://www.mrexcel.com/youtube/TMyAAxbUU7s/

How to plot a circle in an Excel XY Chart
Converting degrees to radians in Excel
Building X, Y, Z values for a series of circles in Excel
Using Chat-GPT for Python Code for a 3D Scatter Plot
Adapting the copied code
Marker choices for Python Charts

Table of Contents
(0:00) Problem Statement – 3D Scatterplot in Python
(0:14) Excel formulas to plot a circle in Excel
(0:45) Convert degrees to Radians in Excel
(1:15) Formulas to Create a 3D Spiral in Excel
(2:45) Finding Python code from Chat-GPT
(3:10) Adapting Python code for Excel
(4:45) Continue reading “Excel Python 3D Scatterplot – Episode 2620” →

The Future of Data Analytics – AI Enhanced #codeinterpreter #machinelearning #hiddeninsights

Posted on by Annie D

You know what I think is the future of data analytics? It’s not just Power BI with its dynamic visuals, nor is it just Excel with its unmatched ad-hoc capabilities. It’s the fusion of these with AI-driven tools like code interpreters. These AI tools guide our insights, supercharging our analysis, making everything more efficient. Imagine blending all these tools, maximizing every ounce of their potential. That’s the future I see. Dive deeper with me and let’s shape this future together!

#powerbi #excel #chatgpt #codeinterpreter

Wait! We can use Databricks data with Microsoft Fabric???

Posted on by Annie D

Ready to learn how you can use Databricks with Microsoft Fabric? Patrick shows you how you can integrate the data and enable the MAGIC of Fabric while leveraging your existing Databricks implementation.

Using Azure Databricks with Microsoft Fabric and OneLake
https://blog.fabric.microsoft.com/en-us/blog/using-azure-databricks-with-microsoft-fabric-and-onelake

📢 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
— rel=”nofollow”>https://www.instagram.com/guyinacube/
— https://guyinacube.com

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

#MicrosoftFabric #Databricks #GuyInACube