Exploring modern analytics, I’ve delved deep into Power BI, Excel, and AI-driven tools. The verdict? While Power BI offers dynamic visuals, Excel shines for ad-hoc tasks. But the game-changer? AI’s code interpreter, guiding insights and supercharging analysis. The future? A blend of these tools, maximizing efficiency and insights. Dive deeper with me to unlock the full potential of data analytics
Category: Business
Excel Python Custom Function – 2617
Microsoft Excel Tutorial: Using a function in Python for Excel calculations.
The problem today: Count how many times a word occurs in a cell in Excel.
To download this workbook: https://www.mrexcel.com/youtube/6Ydb6rIls6M/
The first solution is a series of six formulas in Excel, including SUBSTITUTE, LEN, and more. While it is complicated in Excel, there is a much easier way in Python, using the .Count function. So, Python has a simpler version but how do you call the function from Excel?
After the Python solution, I used the Excel Labs add-in to convert the original six formulas to a LAMBDA.
Other Continue reading “Excel Python Custom Function – 2617”
Dynamic Range in SUMIFS & SUMIF functions using XLOOKUP Function. Excel Magic Trick 1836
Download Excel File: https://excelisfun.net/files/EMT1835-1837.xlsx
Learn about how to create a dynamic range in the SUMIFS and SUMIF functions. Trick works in SUMIFS, SUMIF, MAXIFS, MINIFS, AVERAGEIFS, COUNTIFS or COUNTIF.
Topics:
1. (00:00) Introduction
2. (00:15) Goal of formula
3. (00:28) CHOOSECOLS and XLOOKUP to lookup a column
4. (01:12) CTRL + C, C keyboard shortcut
5. (02:10) SUMIFS with XLOOKUP
6. (02:53) SUMIFS with XLOOKUP
7. (03:18) SUMIF with XLOOKUP
8. (04:02) SUMIF with INDEX
9. (04:29) Array inside SUM
10. (04:44) Summary
11. (05:06) Lookup row in SUMIFS and SUMIF
12. (05:32) Closing, Video Links
#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #powerbi #powerquery #powerbidesktop #freeclass #freecourse #freeclasses #excelclasses #powerquery #powerquerytutorial Continue reading “Dynamic Range in SUMIFS & SUMIF functions using XLOOKUP Function. Excel Magic Trick 1836”
Python Pivot Tables In Excel -Episode 2616
Microsoft Excel Tutorial: Using Python in Excel to create Excel-like pivot tables.
I love pivot tables in Excel. In fact, I’ve written an entire book on Pivot Tables. So when I saw that Python has a function to generate “Excel-like” Pivot Tables in a new data frame, I wanted to try it out.
The Python Pivot Table is missing a few things:
1. Row Fields are called Index
2. Defaults to Average instead of Sum
3. Empty cells show as errors. Use fill_value
4. No Grand Totals by default! Turn on with margins=True
5. When you add Grand Totals, they are called “All” Unless you change them Continue reading “Python Pivot Tables In Excel -Episode 2616”
Power BI has Copilot already? For DAX it does!
We saw the video for Copilot in Microsoft Fabric. Copilot for Power BI is coming! But is it here already? Patrick shows one Copilot experience that’s already in Power BI Desktop!
Quick measure suggestions
https://learn.microsoft.com/power-bi/transform-model/quick-measure-suggestions
📢 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 “Power BI has Copilot already? For DAX it does!”
Gaining Confidence With Python In Excel – 2615
Microsoft Excel Tutorial: Python in Excel
After using Python in Excel for 2 days, I am gaining confidence. Topics today:
• Python Tips from Day 2
• Slow rollout of Python
• Paying for Anaconda
• Python Libraries to explore
• How to write Python results back to Excel grid
• Real-life K-Means clustering with 18K customers, 30K transactions, 300 products
To download the first workbook: https://www.mrexcel.com/youtube/GMF1E0dmjWs/
The second workbook contains actual customer data and I am not sharing it at this time.
Table of Contents
(0:00) Welcome
(0:45) Keyboard shortcuts for Python
(1:22) Leila tip DF.Customer eliminates square bracket notation
(2:14) Seaborn Library of Charts as shown by Mynda
(2:49) Continue reading “Gaining Confidence With Python In Excel – 2615”
Excel Magic Trick 1835 Part 02: Lookup ROW with CHOOSECOLS, INDEX, XLOOKUP or FILTER function?
Download Excel File: https://excelisfun.net/files/EMT1835-1837.xlsx
Learn about how to lookup a row using CHOOSECOLS, INDEX, FILTER or XLOOKUP functions.
Topics:
1. (00:00) Introduction
2. (00:19) Viewer Question
3. (00:38) CHOOSEROWS and XMATCH
4. (01:23) XLOOKUP
5. (02:06) INDEX and XMATCH
6. (03:00) FILTER
7. (03:37) See that all these methods work in SUM function to add sales for the month
8. (04:01) Closing, Video Links
#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #powerbi #powerquery #powerbidesktop #freeclass #freecourse #freeclasses #excelclasses #powerquery #powerquerytutorial #microsoftexcel #microsoftmvp #lookup #xlookup #vlookup #vlookupformula
Get your measures organized in Power BI
As you make more measures in your Power BI Dataset, it can get hard to find the one you need. Sure you can use search… But you can also organize your measures to make it easier to find them. Patrick shows you how!
📢 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 Continue reading “Get your measures organized in Power BI”
Enhancing Analytics with AI: A Deep Dive into Power BI, Excel, and Code Interpreter
In this video, we explore the world of analytics tools, comparing traditional platforms like Power BI and Excel with the cutting-edge capabilities of AI using Code Interpreter. Discover the power of combining these tools to unlock deeper insights, streamline your analysis, and get AI-assisted recommendations that elevate your reporting game. By the end, you’ll understand the true potential of an AI-assisted analytics workflow and how to harness it for yourself.
#Analytics #PowerBI #Excel #CodeInterpreter #AIAssistedAnalytics #DataVisualization #DataInsights #DataStorytelling #AIRecommendations #AnalyticalToolsComparison
Want to dive deeper into AI-enhanced analytics? Subscribe now for more tutorials, comparisons, and tips to revolutionize your data game.
www.enterprisedna.co
Get and use Python Libraries within Microsoft Fabric
Sometimes you need python libraries that aren’t present by default in Microsoft Fabric. Patrick walks you through how to manage your python libraries, so they are available when you need them.
Manage Python libraries in Microsoft Fabric
https://learn.microsoft.com/fabric/data-science/python-guide/python-library-management
wget 3.2
https://pypi.org/project/wget/
📢 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 “Get and use Python Libraries within Microsoft Fabric”
Python In Excel – Getting Started – 2614
Microsoft Excel Tutorial – Using Python in Excel.
Today, August 22, 2023, Microsoft will release a preview of Python in Excel. It is a big day for me… I’ve been trying unsuccessfully to learn Python for ten years. Once Microsoft added it to Excel, I finally have some cool things working.
To download this workbook: https://www.mrexcel.com/youtube/KIhDQDtvZPg/
In this video: a getting started with Python in Excel tutorial.
How to open the Python editor in Excel
Ctrl+Enter versus Enter in the Python Editor in Excel
Returning a Value or a Python Object
Referring to an Excel range in Python
Using Variables in Python in Excel
Plotting Continue reading “Python In Excel – Getting Started – 2614”
Lookup Column with CHOOSECOLS, INDEX or XLOOKUP function? Excel Magic Trick 1835
Download Excel File: https://excelisfun.net/files/EMT1835-1837.xlsx
Learn about how to lookup a column using CHOOSECOLS, INDEX or XLOOKUP functions.
Topics:
1. (00:00) Introduction
2. (00:10) CHOOSECOLS and XMATCH
3. (01:10) XLOOKUP
4. (01:43) INDEX and XMATCH
5. (03:20) Closing, Video Links
#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #powerbi #powerquery #powerbidesktop #freeclass #freecourse #freeclasses #excelclasses #powerquery #powerquerytutorial #microsoftexcel #microsoftmvp #lookup #xlookup #vlookup #vlookupformula