Month: January 2023

What is a Slowly Changing Dimension???

Posted on by Annie D

Want to know more about a slowly changing dimension? What about the different types? Patrick goes through examples and ties in the surrogate key!

Sample: https://github.com/guyinacube/demo-files/blob/master/video%20demos/Synapse/20230124%20-%20SCD%20Demo.sql

Handling Type II Dimension with the MERGE Statement
https://sqldownsouth.blogspot.com/2010/11/handling-type-ii-dimension-with-merge.html

Slowly Changing Dimension
https://en.wikipedia.org/wiki/Slowly_changing_dimension

#PowerBI #GuyInACube

Excel Power Query Import And Clean Fixed Width Text Files 2359

Posted on by Annie D

I was doing a seminar recently and many people at the company were getting reports downloaded as fixed width text files.

I encouraged them to start using the Get & Transform tools in Excel for cleaning this date. After the seminar, I created a text file with many of the issues that were in their workbooks.

Table of Contents
(0:00) Fixed Width Text File for Excel
(0:50) Excel Text Import Wizard UI for Marking Column Locations
(1:19) Finding column start locations for Power Query
(1:59) Importing to Excel from Text/CSV
(2:18) Power Query incorrectly guesses comma as delimiter
(2:42) Power Query make column wider
(2:55) Power Query removing Continue reading “Excel Power Query Import And Clean Fixed Width Text Files 2359” →

How To Create Dynamic Tables In Power BI Using RANKX

Posted on by Annie D

In this tutorial, Sam runs through how to create a dynamic table that only shows the top results across a range of measures. You can do this using a combination of DAX formula including the RANKX function. This is great ranking technique for a number of analytical scenarios in Power BI.

*****Video Details*****
00:00 Introduction
01:00 The goal
01:41 Profit growth YOY
03:40 Sales YOY
04:26 Margin growth YOY

***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI – www.enterprisedna.co/courses/ultimate-beginners-guide-to-power-bi
FREE COURSE – Ultimate Beginners Guide To DAX – www.enterprisedna.co/courses/ultimate-beginners-guide-to-dax
FREE – Power BI Resources – www.enterprisedna.co/power-bi-resources
Enterprise DNA On-Demand – app.enterprisedna.co
Enterprise DNA Subscription Continue reading “How To Create Dynamic Tables In Power BI Using RANKX” →

Remove All Excel Format to Help Fix Reports and Data Errors. Excel Magic Trick 1814 #Shorts

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1814-1815.xlsx
Learn how to remove all formatting, but leave the content in an Excel Worksheet.
Topics:
1. (00:00) Introduction
2. (00:05) Why Remove Formats?
3. (00:18) Apply Normal Style
4. (00:31) Clear Formats
5. (00:38) Keyboard shortcut #1
6. (00:56) Keyboard shortcut #2
7. (01:04) Keyboard shortcut #3
8. (01:24) Find and Fix “Numbers Stored As Text” Data Error
9. (01:45) Keyboard to convert Text Numbers back to Numbers
10. (01:49) Closing, Video Links

#shorts

https://excelisfun.net/files/12-M365ExcelClass.pdf

Excel Average Of Non-Zero Rows In Pivot Table – 2358

Posted on by Annie D

There are 7 pivot table secrets in this video, and you haven’t seen six of them. The question: I have several projects in a pivot table. At the bottom, as part of the pivot table, I want an average of the non-zero rows.

The person is currently using AVERAGEIF outside of the pivot table, but that formula must be adjusted.

Today, we use a Data Model pivot table and a DAX Measure to calculate the average. The resulting pivot table has too many rows, so I use a new Set based on Rows.

I can not post the final formula here Continue reading “Excel Average Of Non-Zero Rows In Pivot Table – 2358” →

How To Change Size Of Visual Based On Ranking Selection In Power BI Using DAX [2023 Update]

Posted on by Annie D

In this tutorial, Sam demonstrates a cool data visualization technique RANX to change the size of a visual based on a ranking selection. Once you understand the patterns there are multiple ways you could apply this to other scenarios.

*****Video Details*****
00:00 Introduction
01:01 Supporting table
02:53 Customer ranking
04:15 Incorporating the measures

***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI – www.enterprisedna.co/courses/ultimate-beginners-guide-to-power-bi
FREE COURSE – Ultimate Beginners Guide To DAX – www.enterprisedna.co/courses/ultimate-beginners-guide-to-dax
FREE – Power BI Resources – www.enterprisedna.co/power-bi-resources
Enterprise DNA On-Demand – app.enterprisedna.co
Enterprise DNA Subscription – app.enterprisedna.co/pricing
Enterprise DNA Events – www.enterprisedna.co/events

#EnterpriseDNA #PowerBI #PowerBIDesktop #PowerBITutorial #DAX #DAXTutorial

Average Difference Between Ship Date Column & Order Date Column – Excel Magic Trick 1813 #Shorts

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1813.xlsx
Create Single Cell Formulas to calculate the average number of days to ship an order based on a Ship Date Column and a Order Date Column. Learn a Microsoft Excel 365 Formula and a formula that works in any version.
Topics:
1. (00:00) Introduction
2. (00:10) Helper Column Method.
3. (02:13) Microsoft 365 Excel and Excel 2021 Formula
4. () Formula that works in all versions of Excel
5. (02:34) Summary, Closing, Video Links

Allow users to Show and Hide columns in a Power BI Paginated report!

Posted on by Annie D

In a Power BI Paginated Report, you can allow your report consumers to customize what they see in the report such as which columns they want in their table or matrix. Patrick shows you how!

Sample RDLs: https://github.com/guyinacube/demo-files/blob/master/video%20demos/RDL/20230118%20-%20Paginated%20Show%20Hide%20Columns.zip

#PowerBI #Paginated #GuyInACube

How To Build A Google News Aggregator In Power BI Using Python

Posted on by Annie D

While most of its users use Power BI to analyze their own data, the ‘Get Data from Website’ functionality makes it easy to analyze external data. In this video, Gaelim is going to show how you can build a Google News aggregator in Power BI using Python which visualizes news that might be relevant to your report.

***** Video Details *****
00:00 Introduction
00:19 End result
02:00 Data set
02:47 Installing Google News library
03:48 Using the News Crawler
06:34 Using Loops

***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI – www.enterprisedna.co/courses/ultimate-beginners-guide-to-power-bi
FREE COURSE – Ultimate Beginners Guide To DAX – www.enterprisedna.co/courses/ultimate-beginners-guide-to-dax
FREE Continue reading “How To Build A Google News Aggregator In Power BI Using Python” →

Excel Keeps Inserting A Space In the Name WITHA – 2356

Posted on by Annie D

We have a person in our roster with the first name of “Witha”. But Excel constantly changes this to “With A”. We are being stung by one of the thousands of entries in the Excel AutoCorrect Options dialog.

This video will show you how to stop Excel from autocorrecting Witha to With A.

What is a Common Table Expression (CTE) and how do you use them?

Posted on by Annie D

We’ve had some questions about what a Common Table Expression (CTE) is. Patrick describes what they are and how you can use them in SQL Server or Azure Synapse Analytics.

WITH common_table_expression (Transact-SQL)
https://learn.microsoft.com/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver16

Sample Scripts – https://github.com/guyinacube/demo-files/tree/master/video%20demos/Synapse

📢 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/
— Continue reading “What is a Common Table Expression (CTE) and how do you use them?” →