Month: September 2023

Excel Hack Use Filter Search Box to Remove Items from Filter! – Episode 2623 #excel #excelhacks

Posted on by Annie D

Microsoft Excel Tutorial: How to use Excel’s Filter Search Box to remove items from Filter.

Melvin from Orlando shared a great Excel trick with me during yesterday’s live Power Excel seminar in Daytona Beach.

To download the workbook from today: https://www.mrexcel.com/youtube/Od5IwswzWnI/

We all know you can use the Filter drop-down Search Box to find all cells that contain Apple. But what if you want everything that does not contain Apple? Rather than use the old Text Filters for Does Not Contain, you can use this great trick from Melvin:
1. Search for Apple
2. Uncheck Select All Items
3. Check Add Current Continue reading “Excel Hack Use Filter Search Box to Remove Items from Filter! – Episode 2623 #excel #excelhacks” →

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!” →