Blog

Bonbon's Guide To Data & Tech > Blog

🔴 Microsoft Power BI / Fabric Q&A – LIVE (March 8, 2025) (Member Chat 2nd Half)

Posted on by Annie D

Join us live as we answer your questions about Power BI & Microsoft Fabric! Get your questions in the chat early as we don’t get to all of them.

💥 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 Continue reading “🔴 Microsoft Power BI / Fabric Q&A – LIVE (March 8, 2025) (Member Chat 2nd Half)” →

No VBA Needed : ) Simple Power Query Table.Pivot Function Makes it Simple! Excel Magic Trick 1875

Posted on by Annie D

Link to Amazon M Book: https://www.amazon.com/Transformative-Magic-Power-Query-Excel/dp/1615470832 (Please write a review too)
Download Excel File: https://excelisfun.net/files/EMT1875.xlsx
Learn how to convert to bad data table with records of data in columns to a proper table structure with records in rows using the Pivot Column Power Query Feature. Lean about efficient M Code (functional language behind Power Query) also!

Learn about the difference between the Table.Pivot function and the Table.UnPivot function.

Topics:
1. (00:00) Introduction.
2. (00:26) Compare to what we did in Excel Short Trick 61
3. (01:20) Difference between Power Query Pivot Column feature & Unpivot Column feature: STAR!!!!
4. (01:47) Pivot Continue reading “No VBA Needed : ) Simple Power Query Table.Pivot Function Makes it Simple! Excel Magic Trick 1875” →

Unleash Data Governance with Microsoft Purview & Microsoft Fabric

Posted on by Annie D

Discover how Microsoft Purview and Microsoft Fabric work together to strengthen data governance, streamline cataloging, and enhance analytics. Marthe gives a look at some ways that Purview can help in your Microsoft Fabric and Power BI journey.

Purview overview
https://learn.microsoft.com/purview/purview

Learn about Microsoft Purview Unified Catalog
https://learn.microsoft.com/purview/unified-catalog

📢 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
— Continue reading “Unleash Data Governance with Microsoft Purview & Microsoft Fabric” →

FILTER & XLOOKUP to Match One Column, Get All TRUEs in Column & Return All Row Headers. EMT 1874

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1874.xlsx
Learn how to do a complicated reverse lookup with a simple formula using the XLOOKUP and FILTER functions.
Topics:
1. (00:00) Introduction.
2. () Formula for reverse lookup with a simple solution using the XLOOKUP and FILTER functions
3. (10:31) Summary
4. (11:12) Closing

#excel #microsoft #excelisfun #excelcourse

What about relationships with Multiple facts in Power BI?

Posted on by Annie D

Now relationships are causing a problem with multiple facts in Power BI! Patrick looks at another way to avoid a circular dependency by way of relationships.

Demo Files:
https://github.com/guyinacube/demo-files/blob/master/video%20demos/PBIX/2025/20250305%20-%20Multiple%20Facts%20pt3.zip

📢 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 – <a Continue reading “What about relationships with Multiple facts in Power BI?” →

LET Function to Define LAMBDA as f, then Perform Column Iteration With Vertical Spill. #Short 67

Posted on by Annie D

Download Excel file: https://people.highline.edu/mgirvin/AllClasses/455/ch11/ExcelShorst64-67.xlsx
Learn how to create a single cell formula with the functions LET and LAMBDA and BYCOL that will iterate columns in an array and spill multiple aggregate results vertically. LET will use the LAMBDA function to define a reusable function, where all you have to do is enter the aggregate function name and below each column the aggregate calculation will appear from a spill.
Thanks to ExcelLambda at YouTube for the Excel formula knowledge in this video : )

#excel #microsoft #excelisfun #excelcourses #excelcourse #excelfunctions #excelformula #excelfunction #365excel #office365 #office365training #exceltips #exceltutorial #exceltraining #exceltricks #excelmaster #excelmagictrick Continue reading “LET Function to Define LAMBDA as f, then Perform Column Iteration With Vertical Spill. #Short 67” →

LET & BYCOL Functions to Spill Column Aggregate calculations Vertically. #Short 66

Posted on by Annie D

Download Excel file: https://people.highline.edu/mgirvin/AllClasses/455/ch11/ExcelShorst64-67.xlsx
Learn how to create a single cell formula with the functions LET and BYCOL that will iterate columns in an array and spill multiple aggregate results vertically. The LET function defines an array and a function, which allows the array to be altered for calculations such as Geometric Mean. Thanks to ExcelLambda at YouTube for the Excel formula knowledge in this video : )

#excel #microsoft #excelisfun #excelcourses #excelcourse #excelfunctions #excelformula #excelfunction #365excel #office365 #office365training #exceltips #exceltutorial #exceltraining #exceltricks #excelmaster #excelmagictrick #excelmagic #short #excelshort
#bycol #let #letfunction #aggregate #spilling #dynnamicspilledarrayformulas

Clean Data with Copilot in Excel – What Works and What Doesn’t? Episode 2673

Posted on by Annie D

Microsoft Excel Tutorial: Excel Clean Data with Copilot

Microsoft Excel just introduced the Clean Data feature in Win32, bringing AI-powered data cleaning straight to the desktop! This tool, previously available in Excel Online since October 2024, promises to detect and fix inconsistencies in your dataset—like inconsistent text, formatting issues, and more. But does it really work as expected?

In this episode, I put Clean Data to the test with a variety of messy datasets. It does well in some areas, like recognizing spelling variations and fixing number formats. However, it struggles with company name inconsistencies, date issues, and partial data cleaning—sometimes catching Continue reading “Clean Data with Copilot in Excel – What Works and What Doesn’t? Episode 2673” →

Power Query: A Cleaner Way to Import & Appends CSV Files The Transformative Power of M Code EMT 1872

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1872.zip
Learn how to import multiple Csv files and append them into a table while extracting the file name and adding it to each Csv table as a new column. The Power Query User Interface methods can do this, but it creates a cluttered and messy queries. See how to streamline this process with a few lines of Power Query M Code (not hard to do). More importantly, learn how to use the let expression to avoid Custom Function syntax and define a variable that can access any scope variable in a formula with back Continue reading “Power Query: A Cleaner Way to Import & Appends CSV Files The Transformative Power of M Code EMT 1872” →

Spill Aggregate Totals with the AGGREAGATE Function. #Short 65

Posted on by Annie D

Download Excel file: https://people.highline.edu/mgirvin/AllClasses/455/ch11/ExcelShorst64-67.xlsx
Learn how to create dynamic spilled array formula to spill multiple aggregate clauctae using the AGGREGATE Function (Excel 2010 Function). This is an old school function that can do amazing things in the Era of Dynamic Spilled Array Fomulas.

#excel #microsoft #excelisfun #excelcourses #excelcourse #excelfunctions #excelformula #excelfunction #365excel #office365 #office365training #exceltips #exceltutorial #exceltraining #exceltricks #excelmaster #excelmagictrick #excelmagic #short #excelshort

AGGREGATE Function, Excel 2010, Spill aggregate formulas, Spill Multiple Different Aggregate Calculations

Spill Aggregate Totals with BYCOL Function (GEOMEAN too), Across Bottom of Table! #Short 64

Posted on by Annie D

Download Excel file: https://people.highline.edu/mgirvin/AllClasses/455/ch11/ExcelShorst64-67.xlsx
Learn how to create a spilled Excel array formula that can calculate an aggregate total for multiple columns. Learn about the BYCOLS function and even see how to spill Geometric Mean calculations for stock values..

#excel #microsoft #excelisfun #excelcourses #excelcourse #excelfunctions #excelformula #excelfunction #365excel #office365 #office365training #exceltips #exceltutorial #exceltraining #exceltricks #excelmaster #excelmagictrick #excelmagic #short #excelshort

Conquer Circular Dependencies: Sort Months Effortlessly in Power BI

Posted on by Annie D

Are month-sorting errors driving you mad? In this video, Patrick lifts the curtain on why your semantic model keeps throwing that frustrating circular dependency error. Watch step by step as he reveals the fix—plus a game-changing tip on heading off these issues upstream in Power Query or even at the data source. It’s time to banish those Power BI sorting nightmares and reclaim your data sanity once and for all!

Demo File:
https://github.com/guyinacube/demo-files/blob/master/video%20demos/PBIX/2025/20250227%20-%20Month%20will%20not%20sort.zip

📢 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 “Conquer Circular Dependencies: Sort Months Effortlessly in Power BI” →