Blog

Bonbon's Guide To Data & Tech > Blog
rank in Access

MSPTDA 16: Power BI Desktop Comprehensive Introduction: Power Query, DAX, Dashboards, Publishing

Posted on by Annie D

Download Zipped Folder with Text Files & Excel File: https://ift.tt/2L8w0is
Download Power BI Desktop FINISHED File: https://ift.tt/2C070XR
Download pdf Notes about Power Query: https://ift.tt/2L657Mh

This video is a comprehensive lesson in Power BI Desktop: Power Query to import data, DAX Formulas and Relationships to complete Data Model, Creating Dashboards, Publishing and Sharing Reports.

Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.

Topics:
1. (00:15) Introduction of what we will do in this video.
2. (02:25) Overview of Excel Power Pivot & Power BI Desktop
3. (02:44) Approximate History of Power BI Desktop :
4. (03:15) Different Versions of Power BI (Different Power BI Products) Available from Microsoft
5. (04:56)Download Power BI Desktop (link to Avi’s video: https://www.youtube.com/watch?v=5Fv-I9xQkcc)
6. (05:43) List of Charts and Visualizations for your Dashboard (Review from prerequisite classes Busn 216 & 218)
7. (06:02) Overriding Steps for our Project
8. (06:27) Open a blank Power BI File
9. (07:04) Introduction to Power BI Window and User Interface
10. (08:32) Power Query to Import Multiple CSV Files and Clean and Transform Data
11. (13:38) Why we do NOT use Number or Date Fields from a Fact Table
12. (15:57) Import Dimension Tables from a Single Excel File
13. (18:09) Merge Snow Flake Dimension Tables into dProduct Table
14. (19:30) Do NOT import to Data Model (Uncheck Enable Load)
15. (20:22) Old Relationship View & New Relationships View with Properties & Better Selection Capability
16. (20:41) Steps to create Date Table using CALENDAR DAX Table Function & Calculated Columns. See many DAX Functions such as CALENDAR, FORMAT and others.
17. (16:10) Sort By Column to get Months to Sort correctly.
18. (27:47) Create Fiscal Periods for Data Table, including Helper Column for Sorting Fiscal Period correctly.
19. (33:12) Hide Columns from Report View
20. (34:00) Create DAX Measures and see why we do not use Implicit Measures.
21. (36:17) SUMX DAX Function
22. (38:15) Row Context (how formula calculates for each row in a table or Iterator Function)
23. (40:12) Filter Context (How Measures Calculate and how Tables are Filtered when Measures Calculate)
24. (41:50) Measure for Average Daily Revenue. Learn about Context Transition. See AVERAGEX Function to iterate at the Daily level.
25. (47:55) Conventions for DAX Formulas with a great tip from Marco Russo and Albetro Ferrari
26. (49:00) More About Filter Context and Context Transition
27. (49:26) Gross Profit Measures
28. (51:48) Refine Data Model in Power Query by Removing Columns in dProduct Table
29. (52:40) Learn about how to Create & Format Visualizations
30. (52:40) Create “Ave Daily GP” Dashboard.
31. (52:40) Create Matrix and add Conditional Formatting
32. (55:29) Create Column Chart and add Conditional Formatting
33. (56:00) Hierarchies
34. (56:52) Drill Down Icons in Power BI
35. (59:09) Create Line Chart
36. (01:00:00) Create Card
37. (01:01:00) Edit Interactions between visualizations
38. (01:02:50) Create “Fiscal Report” Dashboard
39. (01:05:32) Bookmark to save views of a Dashboard
40. (01:06:20) Create “Ave Last 12 Months” Dashboard
41. (01:06:37) DAX Measure for Average Transactional Revenue. See AVERAGEX Function to iterate at the transaction line item level.
42. (01:07:30) Visual of how we change the Filter Context to get dates for a full year backwards.
43. (01:08:25) CALCULATE & DATESINPERID & LASTDATE DAX Functions to calculate Measure for Rolling 12 Month Average for Transaction Level Data.
44. (01:12:08) Create “Question” Dashboard. Learn about Ask A Question feature.
45. (01:13:08) Publish Report to powerbi.com
46. (01:14:15) Edit at powerbi.com
47. (01:14:34) Publish to Web with Free Power BI Desktop version and allow public to review Report
48. (01:16:15)Publish and Share with Power BI Pro Account
49. (01:17:44) Source Data Changes and Refresh
50. (01:18:18) Summary

View on YouTube

Comprehensive Introduction to Excel Power Pivot, DAX Formulas and DAX Functions

Posted on by Annie D

Download Excel START File: https://ift.tt/2FrxeX5
Second Excel Start File: https://ift.tt/2Dtf4Sf
Download Zipped Folder with Text Files: https://ift.tt/2Frxfu7
Download Excel FINISHED File: https://ift.tt/2qSnYkx
Download pdf Notes about Power Query: https://ift.tt/2FrxwgD
Assigned Homework:
Download Excel File with Instructions for Homework: https://ift.tt/2qRom2T
Examples of Finished Homework: https://ift.tt/2Frxgyb

This video teaches everything you need to know about Power Pivot, Data Modeling and building DAX Formulas, including all the gotchas that most Introductory videos do not teach you!!!

Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.

Topics:
(00:15) Introduction & Overview of Topics in Two Hour Video
1. (04:36) Standard PivotTable or Data Model PivotTable?
2. (05:51) Excel Power Pivot & Power BI Desktop?
3. (12:31) Power Query to Extract, Transform and Load Data to Data Model – Get data from Text Files, Relational Database and Excel File.
4. (25:47) Build Relationships
5. (27:43) Introduction to DAX Formulas: Measures & Calculated Columns
6. (29:15) DAX Calculated Column using the DAX Functions, RELATED and ROUND
7. (31:20) Row Context: How DAX Calculated Columns are Calculated: Row Context
8. (33:49) We do not want to use Calculated Column results in PivotTable using Implicit Measures
9. (34:05) DAX Measure to add results from Calculated Column, using DAX SUM Function.
10. (35:29) Number Formatting for DAX Measures
11. (36:35) Data Model PivotTable
12. (39:31) Explicit DAX Formulas rather than Implicit DAX Formulas
13. (41:50) Show Implicit Measures
14. (45:00) Filter Context (First Look) How DAX Measures are Calculated
15. (50:14) Drag Columns from Fact Table or Dimension Table?
16. (53:30) Hiding Columns and Tables from Client Tool
17. (55:52) Use Power Query to Refine Data Model
18. (57:54) SUMX Function (Iterator Function). DAX Measure for Revenue using the SUMX Function to simulate Calculated Columns in DAX Measures
19. (01:01:00) Compare and Contrast Calculated Columns & Measures
20. (01:04:26) Why We Need a Date Table. Why we do NOT use the Automatic Grouping Feature for a Data Model PivotTable
21. (01:06:46) Build an Automatic Date Table in Excel Power Pivot. And then build Relationship.
22. (01:11:00) Introduction to Time Intelligence DAX Functions. See TOTALYTD DAX Function
23. (01:13:47) Introduction to CALCULATE Function: Function that can “see” Data Model and can change the Filter Context. (01:18:00) Also see the ALL and DIVIDE DAX Functions. Create formula for “% of Grand Total”. Also learn about (01:21:30) Context Transition and the Hidden CALCULATE on all Measures.
24. (01:27:18) DAX Formula Benefits.
25. (01:28:00) Example of DAX Formula that is easier to author than if we tried to do it with a Standard Pivot Table or Array Formulas
26. (01:31:50) AVERAGEX Function (Iterator Function) to calculate Average Daily Revenue.
27. (01:34:00) Filter Context (Second Look) AVERAGEX Iterator Function
28. (01:36:16) Build Dashboard. Create multiple DAX Formulas. Create Multiple Data Model PivotTables and a Data Model Chart.
29. (01:38:38) Create Measures for Gross Profit and Gross Profit %
30. (01:41:27) Continue making more Data Model PivotTables.
31. (01:41:50) Make Data Model Pivot Chart.
32. (01:45:10) Conditional Formatting for Data Model PivotTable.
33. (01:46:22) DAX Text Formula for title of Dashboard
34. (01:47:50) CUBE Function to Convert Data Model PivotTable to Excel Spreadsheet Formulas.
35. (01:50:05) Adding New Data and Refreshing.
36. (01:50:40) Update Excel Power Pivot Automatic Date (Calendar) Table. Clue is the blank in the Dimension Table Filter.
37. (01:52:20) How to Double Check that a DAX Formula is yielding the correct answer?
38. (01:53:22) DAX Table Functions. See CALCULATETABLE DAX Function.
39. (01:55:07) DAX Studio to visualize DAX Table Functions, and to efficiently create DAX Formulas
40. (02:00:12) Existing Connections to import data from Data Model into an Excel Sheet
(02:03:15) Summary

View on YouTube

PivotTables Can’t, But PIVOTBY & GROUPBY Can ! 10 Mind-Blowing Examples! MS 365 Excel Basics #9

Posted on by Annie D

Download Excel File: https://people.highline.edu/mgirvin/AllClasses/218M365/Content/ExcelBasics09.xlsx
Full free YouTube class: https://www.youtube.com/playlist?list=PLrRPvpgDmw0k7ocn_EnBaSJ6RwLDOZdfo
Read (download right-click): pdf notes: https://people.highline.edu/mgirvin/AllClasses/218M365/Content/ExcelBasics09.pdf
The Only App That Matters book by Mike Girvib at Amazon: https://www.amazon.com/stores/Mike-excelisfun-Girvin/author/B009Q5T4P2?ccs_id=70eb6de0-afb4-4979-a9a7-a1bdc0089ad3
Link to LAMBDA video:
https://www.youtube.com/watch?v=OxV-F0vXj8I&list=PLrRPvpgDmw0nre_bTeBfJWjrnixKoyNtW&index=13

In this video learn about PIVOTBY & GROUPBY Functions:
Topics:
1. (00:00) Introduction
2. (00:46) Topics in video
3. (01:48) Full list of Array Functions seen in this video
4. (02:10) Fundamental of Dynamic Spilled Array Formulas. Learn about the SEQUENCE array function.
5. (06:17) Conditional Formatting for Dynamic Spilled Array Formulas
6. (07:40) GROUPBY & PIVOTBY arguments
7. ( 08:07) History of Single Cell Reporting Formulas
8. Continue reading “PivotTables Can’t, But PIVOTBY & GROUPBY Can ! 10 Mind-Blowing Examples! MS 365 Excel Basics #9” →

Mind-Blowing: GROUPBY Function Beats PivotTable, Hands-down. Excel Short Magic Trick 75

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/ExcelShort68.xlsx
Full Length GROUPBY video with much more detail: https://www.youtube.com/watch?v=l5hcjfW81tQ&list=PLrRPvpgDmw0k7ocn_EnBaSJ6RwLDOZdfo&index=12
Link to full Excel Basics Class: https://www.youtube.com/watch?v=rXwZITn70Xo&list=PLrRPvpgDmw0k7ocn_EnBaSJ6RwLDOZdfo&index=9

Using the gear icon setting button below the video, you can watch subtitles in these languages: Afrikaans, Arabic, Bengali (some videos), Bangla, Dutch, Filipino, French, Hindi, Indonesian, Khmer, Malay, Malayalam, Nepali, Persian, Polish, Spanish, Swahili, Tamil, Telugu, Thai, Tibetan, Urdu, Vietnamese.
Using the gear icon setting button below the video, you can listen to an audio track in these translated languages: French, German, Hindi, Indonesian, Italian, Japanese, Portuguese, and Spanish.

#dataanalysis #excel #excel365 #office365 #office365training Continue reading “Mind-Blowing: GROUPBY Function Beats PivotTable, Hands-down. Excel Short Magic Trick 75” →

Temporary Fix for GROUPBY Function & Single Cell Reporting Formula Excel Chart Bug? EMT 1881

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1881.xlsx
Enny Kraft from YouTube and Hamidi Hamid from LinkedIn provide temporary fixes for the chart & GROUPBY bug brought up in EMT 1880:
When I create a single cell reporting formula with the GROUPBY array function or other single cell dynamic spilled array reporting formula, and then create an Excel Chart from the single cell formula, and then insert a column, the chart looses track of the correct location for the report. The chart breaks and poinst to the incorrect range.
Topics:
1. (00:00) Introduction
2. (00:01) What is the Bug?
3. (00:26) Hamidi Hamid fix
4. (00:46) Enny Continue reading “Temporary Fix for GROUPBY Function & Single Cell Reporting Formula Excel Chart Bug? EMT 1881” →

Let Copilot Fly Your Power Query in Microsoft Fabric

Posted on by Annie D

Let’s dive into how Copilot supercharges your Power Query experience in Microsoft Fabric, helping you transform data faster than ever. Alex Powers shows us some great examples! Remember—you’re the pilot, so let’s get in there and let Copilot do the heavy lifting!

Alex Powers:
https://bsky.app/profile/itsnotaboutthecell.com
https://www.linkedin.com/in/alexmpowers/

Engage in the Reddit community!
https://www.reddit.com/r/MicrosoftFabric/
https://www.reddit.com/r/PowerBI/

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

— <a href="https://bsky.app/profile/guyinacube.bsky.social" Continue reading “Let Copilot Fly Your Power Query in Microsoft Fabric” →

From Power BI to AI and How Tech Is Rewriting the Rules of Development with Brian Julius

Posted on by Annie D

***** Video Details *****
In this video, we dive deep into how advancements in artificial intelligence are transforming the software development landscape. From Power BI integrations to the rise of AI coding assistants like OpenRouter and the adoption of Vibe Coding, discover how developers are rethinking traditional workflows.

***** Related Links *****
https://powerbi.microsoft.com/
https://openrouter.ai/
https://notebooklm.google/
https://lovable.dev/
https://codeium.com/windsurf
https://supabase.com/

***** Learning with Enterprise DNA *****
FREE Courses – https://bit.ly/45fu3tw
FREE Resources – https://bit.ly/455Hw6O
EDNA Learn – https://app.enterprisedna.co/app
Data Mentor – https://mentor.enterprisedna.co/
EDNA Chat – Continue reading “From Power BI to AI and How Tech Is Rewriting the Rules of Development with Brian Julius” →

No Copilot? No Problem: Power Query’s AI to the Rescue!

Posted on by Annie D

If you’re missing Copilot in Microsoft Fabric, don’t sweat it! Power Query is packed with AI features that can still supercharge your data game. In this video, Alex Powers explores how to leverage these tools to keep your workflow smooth and powerful, no Copilot required!

Alex Powers:
https://bsky.app/profile/itsnotaboutthecell.com
https://www.linkedin.com/in/alexmpowers/

Engage in the Reddit community!
https://www.reddit.com/r/MicrosoftFabric/
https://www.reddit.com/r/PowerBI/

📢 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 Continue reading “No Copilot? No Problem: Power Query’s AI to the Rescue!” →

Excel Revolution with PIVOTBY & GROUPBY Functions! #Short Preview of Upcoming Video

Posted on by Annie D

Excel Basics Free Class: https://www.youtube.com/playlist?list=PLrRPvpgDmw0k7ocn_EnBaSJ6RwLDOZdfo
PivotTables revolutionized reporting 40 years ago when they debuted in 1994 in Excel 5. 40 years later, in 2024, Microsoft introduced the PIVOTBY and GROUPBY functions, in 2024 to revolutionize reporting for our future generations!
PivotTables to create summary reports. They require a refresh when source data changes.
PIVOTBY Dynamic Spilled Array Formula to create summary reports. They instantly update when source data changes.
GROUPBY Dynamic Spilled Array Formula to create summary reports. They instantly update when source data changes.
Video has subtitles in these languages: Afrikaans, Arabic, Bengali, Bangla, India), Dutch, Filipino, French, Hindi, Track, Indonesian, Continue reading “Excel Revolution with PIVOTBY & GROUPBY Functions! #Short Preview of Upcoming Video” →

Excel Revolution with PIVOTBY & GROUPBY Functions! #Short Preview of Upcoming Video

Posted on by Annie D

Excel Basics Free Class: https://www.youtube.com/playlist?list=PLrRPvpgDmw0k7ocn_EnBaSJ6RwLDOZdfo

PivotTables revolutionized reporting 40 years ago when they debuted in 1994 in Excel 5.

40 years later, in 2024, Microsoft introduced the PIVOTBY and GROUPBY functions, in 2024 to revolutionize reporting for our future generations!

PivotTables to create summary reports. They require a refresh when source data changes.

PIVOTBY Dynamic Spilled Array Formula to create summary reports. They instantly update when source data changes.

GROUPBY Dynamic Spilled Array Formula to create summary reports. They instantly update when source data changes.

Video has subtitles in these languages: Afrikaans, Arabic, Bengali, Bangla, India), Dutch, Filipino, French, Hindi, Track, Indonesian, Khmer, Continue reading “Excel Revolution with PIVOTBY & GROUPBY Functions! #Short Preview of Upcoming Video” →

How to Add Subtitles to YouTube Videos in Multiple Language March 25 2025 Update

Posted on by Annie D

Learn how to add subtitles to your YouTube video. You can add subtitles in many languages.

excelisfun YouTub home page: https://www.youtube.com/user/excelisfun

Topics:
(00:00) Intro
(00:03) March 25, 2025 YouTube Subtotal Update
(01:21) What to do if Auto-translate option is greyed out
(02:22) Summary

For example, this video has subtitles in these languages: Afrikaans, Arabic, Bengali, Bangla, India), Dutch, Filipino, French, Hindi, Track, Indonesian, Khmer, Malay, Nepali, Persian, Polish, Spanish, Swahili, Tamil, Thai, Urdu, Vietnamese.
The steps to add subtitles are: Go to YouTube Studio, Go to Content, Hover over video, click the detail button, in the upper left click Language, on the right click Add Continue reading “How to Add Subtitles to YouTube Videos in Multiple Language March 25 2025 Update” →

Add Slicer to PivotTable & Chart To Enable Quick Analysis Excel Short Magic Trick 74

Posted on by Annie D

In this short, learn how to add a Slicer to a PivotTable and Chart to Allow Quick Analysis. This is an outtake from Excel Basics Data 08, Introduction to Data Analysis:
Download Excel File: https://people.highline.edu/mgirvin/AllClasses/218M365/Content/ExcelBasics08.xlsx
Pdf notes (free book): https://people.highline.edu/mgirvin/AllClasses/218M365/Content/ExcelBasics08.pdf
Link to full Excel Basics Class: https://www.youtube.com/watch?v=rXwZITn70Xo&list=PLrRPvpgDmw0k7ocn_EnBaSJ6RwLDOZdfo&index=9
Link to full Data Analysis Class: https://www.youtube.com/watch?v=rXwZITn70Xo&list=PLrRPvpgDmw0kCv2ulsHk4uitL9meshr_k&index=1

Video has subtitles in these languages: Afrikaans, Arabic, Bangla, Bangla (India), Dutch, Filipino, Hindi, Indonesian, Khmer, Malay, Swahili, Thai, Urdu, Vietnamese

#dataanalysis #excel #excel365 #office365 #office365training #excelisfun #pivot_table #pivot #pivottable #pivottables #charts #excelcharts #slicer #excelslicer #slicers #keyboardshortcuts #excelbasics #excelbasicsforbeginners #slicerchart? Continue reading “Add Slicer to PivotTable & Chart To Enable Quick Analysis Excel Short Magic Trick 74” →

Why Excel Table Feature is Amazing & How To Create Them: Excel #Short Magic Trick 72

Posted on by Annie D

In this short, learn how and why the Excel Table feature is so amazing.

Download Excel File: https://people.highline.edu/mgirvin/AllClasses/218M365/Content/ExcelBasics08.xlsx

This is an outtake from Excel Basics Data 08, Introduction to Data Analysis:
Link to full Excel Basics Class: https://www.youtube.com/watch?v=rXwZITn70Xo&list=PLrRPvpgDmw0k7ocn_EnBaSJ6RwLDOZdfo&index=9
Link to full Data Analysis Class: https://www.youtube.com/watch?v=rXwZITn70Xo&list=PLrRPvpgDmw0kCv2ulsHk4uitL9meshr_k&index=1

Video has subtitles in these languages: Afrikaans, Arabic, Bangla, Bangla (India), Dutch, Filipino, Hindi, Indonesian, Khmer, Malay, Swahili, Thai, Urdu, Vietnamese

#dataanalysis #excel #excel365 #office365 #office365training #excelisfun #exceltables