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

New productivity workflow for learning anything new! Real time voice

Posted on by Annie D

***** Video Details *****
In this video, I share essential tips and strategies for debugging and managing small issues when building a new web app. From planning thoroughly to leveraging AI tools like lovable.dev, I discuss how to streamline your development process, handle project evolution, and focus on high-level product creation through natural language prompting. Whether you’re a seasoned developer or just starting out, these insights can help make your web development journey smoother and more efficient.

Timestamps:
0:00 Introduction to Debugging in Web Development
0:18 Managing Small Issues and Seeking Better Approaches
0:33 Tips for Strategic Debugging
1:01 Strategically Managing Development Processes
1:19 Using Natural Language Continue reading “New productivity workflow for learning anything new! Real time voice” →

MS 365 Excel Basics #3: Number Formatting as Façade & when you MUST use ROUND Function

Posted on by Annie D

Download Excel File: https://people.highline.edu/mgirvin/AllClasses/218M365/Content/ExcelBasics03.xlsx
Read (download right-click): pdf notes: https://people.highline.edu/mgirvin/AllClasses/218M365/Content/ExcelBasics03.pdf
In this video learn about how to not be tricked by Number Formatting, and how to use the ROUND function rather than number formatting to calculate accurate payroll, invoicing and income tax calculations. Lean about difference between Currency and Accounting Number Formatting.
Topics:
1. (00:00) Introduction
2. (01:04) What does Number Formatting do?
3. (02:38) Formulas do not see Number Formatting
4. (04:10) Keyboard to apply General Number Formatting (Eraser): Ctrl + Shift + ~ / `
5. (04:40) Number Formatting can save time entering data.
6. (07:40) Currency and Accounting Number Formatting
7. Continue reading “MS 365 Excel Basics #3: Number Formatting as Façade & when you MUST use ROUND Function” →

Excel Which Products Contain Both Of These Items – Episode 2661

Posted on by Annie D

Microsoft Excel Tutorial: Which Products Contain Both Of These Items?

Today, an odd data problem. I have a solution, but I suspect there are much easier ways to do this. I have a list of songs in workouts from Supernatural VR. Over 500 workouts are Quick Hits with just two songs. Each Quick Hits workout should be a subset of a longer workout. Given the two lists, how can you use Excel to find the parent workout?

My solution involves an XLOOKUP with a concatenated key and a concatenated lookup vector. This gives me a list of the Workout IDs that contain Continue reading “Excel Which Products Contain Both Of These Items – Episode 2661” →

MS 365 Excel Basics #2: Add & Count Formulas with COUNT, COUNTA, ROWS, SUM, SUMIFS, COUNTIFS & IF

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/ExcelBasics02.xlsx
pdf notes: https://excelisfun.net/files/ExcelBasics02.pdf
In this video learn every trick there is for Excel formula counting and adding with 10 different Excel Built-in Functions, including how to add and count with a single condition, two or more conditions, an AND Logical Test, and even the elusive OR Logical Test. See many use business examples.
This video is taught by Mike “excelisfun” Girvin, Highline College Profession since 2002 years and excelisfun YouTuber since 2008. This video is #2 in a class of 10 free videos and Excel files! This is a free YouTube class that Continue reading “MS 365 Excel Basics #2: Add & Count Formulas with COUNT, COUNTA, ROWS, SUM, SUMIFS, COUNTIFS & IF” →

Edit your Direct Lake models directly in Power BI Desktop!

Posted on by Annie D

Creating Power BI Direct Lake models? You can now edit your Direct Lake semantic models right in Power BI Desktop! Patrick walks you through how!

Live edit of Direct Lake models in Power BI Desktop
https://powerbi.microsoft.com/en-us/blog/live-edit-of-direct-lake-models-in-power-bi-desktop/

Direct Lake in Power BI Desktop (preview)
https://learn.microsoft.com/fabric/get-started/direct-lake-power-bi-desktop

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

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

#PowerBI #DirectLake #GuyInACube

REPLAY Microsoft Fabric / Power BI Q&A w/ Kasper de Jonge (April 27, 2024)

Posted on by Annie D

Join us live as we answer your questions about Microsoft Fabric & Power BI! With special guest, Kasper de Jonge!

📢 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

Connect with Kasper:
Website: https://www.kasperonbi.com/
YouTube: https://www.youtube.com/@KasperOnBI
Twitter: https://twitter.com/kjonge

📢 Become a member: https://guyinacu.be/membership

*******************

Want to take your Power BI skills to the next level? We have Continue reading “REPLAY Microsoft Fabric / Power BI Q&A w/ Kasper de Jonge (April 27, 2024)” →

REPLAY Let’s talk Microsoft Fabric – LIVE w/ Josh Caplan (May 11, 2024)

Posted on by Annie D

Join us live as we answer your questions about Microsoft Fabric (including Power BI)! 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

Connect with Josh:
Twitter: https://twitter.com/JoshCaplan1984
LinkedIn: <a href="https://www.linkedin.com/in/josh-caplan-2139a3a6/" Continue reading “REPLAY Let’s talk Microsoft Fabric – LIVE w/ Josh Caplan (May 11, 2024)” →

REPLAY Microsoft Fabric / Power BI Q&A – LIVE (May 18, 2024)

Posted on by Annie D

Join us live as we answer your questions about Microsoft Fabric (including Power BI)! 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 “REPLAY Microsoft Fabric / Power BI Q&A – LIVE (May 18, 2024)” →

MS 365 Excel Basics #1 What is Excel?, Formulas, Functions, Formatting, Cell References & Page Setup

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/ExcelBasics01.xlsx
pdf notes: https://excelisfun.net/files/ExcelBasics01.pdf
In this video learn about the different types of formulas and functions in MS 365 Excel, as well as how to so style and number formatting. Learn about all the shortcuts that will allow you to create an Excel solution with lightening speed and accuracy.
In this video get a complete introduction to Excel basics taught by Mike “excelisfun” Girvin, Highline College Profession since 2002 years and excelisfun YouTuber since 2008. This is video is first in a class of 10 free videos and Excel files!
This is a free YouTube Continue reading “MS 365 Excel Basics #1 What is Excel?, Formulas, Functions, Formatting, Cell References & Page Setup” →

Excel Easy Fake Data From Python In Excel – Episode 2660

Posted on by Annie D

Microsoft Excel Tutorial: Excel Fake Data From Python In Excel

Creating fake data with Python in Excel has never been easier! In this video, I walk you through generating random data directly in Excel using Python’s powerful capabilities. We start from a completely blank workbook, search for Python, and dive into some exciting new options like the QR code generator and random data generator. This process is amazing for anyone who needs quick, randomized data sets without leaving Excel. Just a few clicks, and you can have it all right at your fingertips!

We’ll start by setting up Python to generate a Continue reading “Excel Easy Fake Data From Python In Excel – Episode 2660” →

Excel Draw Perfect Shapes With Draw And Hold Gesture – Episode 2659

Posted on by Annie D

Microsoft Excel Tutorial: Draw Perfect Shapes With Draw And Hold Gesture
In this video, I’m diving into a new feature in Microsoft Excel: the Draw and Hold Gesture!
If you’ve ever wanted to create shapes in Excel without struggling with awkward mouse drawing, this update is for you. While the feature has been available in OneNote for a while, it’s finally made its way to Excel and PowerPoint—at least for those on the Insider’s channel. I’ll walk you through how to activate this new tool and show you what it can do!

First, if you don’t see the Draw tab in Continue reading “Excel Draw Perfect Shapes With Draw And Hold Gesture – Episode 2659” →

Power BI Desktop Gets a DARK MODE Makeover!

Posted on by Annie D

We’ve been waiting a long time to get DARK MODE in Power BI Desktop! It’s finally here. Harleen Kaur joins us to show everything you need to know to start using it.

By Popular Demand: Dark mode is now available in Power BI Desktop!
https://powerbi.microsoft.com/blog/by-popular-demand-dark-mode-is-now-available-in-power-bi-desktop/

Create Power BI reports in dark mode
https://learn.microsoft.com/power-bi/create-reports/power-bi-dark-mode

Connect with Harleen Kaur
https://www.linkedin.com/in/-harleen-kaur/
https://x.com/sharlkaur

📢 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: <a href="https://guyinacu.be/courses" Continue reading “Power BI Desktop Gets a DARK MODE Makeover!” →