Tag: How to

MSPTDA 35: Power BI & DAX Formula for Data Modeling with a Slowly Changing Dimensions

Posted on by Annie D

Download Start Power BI File: https://ift.tt/3697log
Download Finished Power BI File: https://ift.tt/31NrAEK
Pdf files with notes: https://ift.tt/2IOoqtJ

In this video learn about how to deal with a “Slowly Changing Dimensions”, where the Dimension = Employee and the changing Attribute = Team. See a solution for reporting Teams and Employee Sales Over Time using the Matrix Report. Most Important, see how to take a Team-Employee Lookup Table and 1) Create a Dimension Table using the SELECTCOLUMNS function and 2) Add Team-Employee Key to Fact Table by performing a three-way lookup using the CALCULATE, VALUES and FILTER Functions. See a second way to do the 3-way lookup in DAX with the SELECTCOLUMNS and FILTER Functions.
Topics:
1. (00:18) Introduction and over view the Slowly Changing Dimensions, tha tasks we will perform and the report we need.
2. (02:25) Take a Team-Employee Lookup Table and create a Dimension Table using the SELECTCOLUMNS function
3. (03:35) Take a Team-Employee Lookup Table and add a Team-Employee Key to Fact Table by performing a three-way lookup using the CALCULATE, VALUES and FILTER Functions.
4. (06:40) How to format DAX Formulas
5. (07:22) Explanation of 3-way lookup function with diagrams
6. (07:58) Create Relationships
7. (08:05) Hide Lookup Table
8. (08:26) Create Report for Teams and Employee Sales Over Time using the Matrix Report.
9. (09:34) BONUS FORMULA: Alternative 3-way lookup in DAX with the SELECTCOLUMNS and FILTER Functions.
10. (09:56) Summary

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

View on YouTube

Start a New Line in the Same Cell in Excel (Shortcut & Formula)

Posted on by Annie D

In this video, I will show you how to start a new line in the same cell in Excel.

If you’re manually typing the data you can do this using the keyboard shortcut ALT + ENTER. Place the cursor where you want the line break, hold the ALT key and then press the Enter key.

You can also use formulas to quickly get a line break and start something in a new line using a formula. In Excel 2016 and later versions you can use the TEXTJOIN formula and in prior versions, you can use the simple CONCATENATE formula or the & sign.

This video is a part of my ‘Excel is two-minute’ series, where I share simple and quick tips to help you speed up your work and be more efficient

Subscribe to this YouTube channel to get updates on Excel Tips and Excel Tutorials videos – https://www.youtube.com/c/trumpexcel

This YouTube channel is managed by Sumit Bansal (who also runs the TrumpExcel website). This channel is meant for people who want to learn Excel.

It covers a lot of Excel basics and advanced Excel topics such as Excel Formulas, Functions, Pivot Table, Shortcuts, Excel VBA, Macros, Excel Dashboards, Excel Charts, Conditional Formatting, Power Query, etc. Please subscribe to this channel to be the first to know when new Excel tutorials come out.

You can find a lot of useful Excel resources on the following site: https://trumpexcel.com/

#Excel #ExcelTips #Excelin2Minutes

View on YouTube

DAX Fridays! #147: ISONORAFTER

Posted on by Annie D

In today’s video we are going to go through the DAX function called ISONORAFTER.
Happy Friday everybody!!

Get Northwind Dataset: https://www.youtube.com/watch?v=k3NMIlLffrU

Link to DAX Fridays survey: http://bit.ly/2MMM4KK

Here you can download all the pbix files: https://ift.tt/2yGx9Ih

SUBSCRIBE to learn more about Power and Excel BI!
https://www.youtube.com/channel/UCJ7UhloHSA4wAqPzyi6TOkw?sub_confirmation=1

☼☼☼☼☼☼☼☼☼☼

POWER BI COURSES:

Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:
https://ift.tt/2NEZM2b

☼☼☼☼☼☼☼☼☼☼

ABOUT CURBAL:
Website: http://www.curbal.com
Contact us: https://ift.tt/2qhiZvU

▼▼▼▼▼▼▼▼▼▼

If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:

https://ift.tt/2NrgChB

Many thanks in advance!Here you can download all the pbix files: https://ift.tt/2yGx9Ih

▲▲▲▲▲▲▲▲▲▲

Our PLAYLISTS:
– Join our DAX Fridays! Series: https://goo.gl/FtUWUX
– Power BI dashboards for beginners: https://goo.gl/9YzyDP
– Power BI Tips & Tricks: https://goo.gl/H6kUbP
– Power Bi and Google Analytics: https://goo.gl/ZNsY8l

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

What gear do I use to make my videos and run my business? Below you will find a list of most of my gear. The links on the store are affiliate links, meaning if you buy something from them, amazon will give a small commission and you will be supporting my channel indirectly. Thanks in advance!

https://ift.tt/2I8nl09

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

QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Linkedin ► https://goo.gl/3VW6Ky
Twitter ► @curbalen, @ruthpozuelo
Facebook ► https://goo.gl/bME2sB

#daxfridays #curbal #SUBSCRIBE

View on YouTube

View & Compare Two Sheets SIDE-BY-SIDE in the Same Excel File

Posted on by Annie D

In this video, I will show you how to view and compare two sheets side by side in Excel (in the same view).

This can be made possible by using the NEW Window functionality in Excel. When you use the New Window option, it creates an exact replica of the workbook.

This means that you have two exact same workbooks open at the same time. Now you can arrange these vertically and view these side by side.

You can also enable synchronous scrolling for each worksheet.

This is a great way to view and compare different sheets in the same Excel file, or view different parts of the dataset in the same view.

This video is a part of my ‘Excel is two-minute’ series, where I share simple and quick tips to help you speed up your work and be more efficient

Subscribe to this YouTube channel to get updates on Excel Tips and Excel Tutorials videos – https://www.youtube.com/c/trumpexcel

This YouTube channel is managed by Sumit Bansal (who also runs the TrumpExcel website). This channel is meant for people who want to learn Excel. It covers a lot of Excel basics and advanced Excel topics such as Excel Formulas, Functions, Pivot Table, Shortcuts, Excel VBA, Macros, Excel Dashboards, Excel Charts, Conditional Formatting, Power Query, etc. Please subscribe to this channel to be the first to know when new Excel tutorials come out.

You can find a lot of useful Excel resources on the following site: https://trumpexcel.com/

#Excel #ExcelTips #Excelin2Minutes

View on YouTube

MSPTDA 34: Data Modeling for Slowly Changing Dimensions with Power Query & Power Pivot

Posted on by Annie D

Download Start Excel file: https://ift.tt/2NdZexR
Download Finished Excel file: https://ift.tt/2MKIXl9
Pdf files with notes: https://ift.tt/2IOoqtJ

In this video learn about how to deal with a “Slowly Changing Dimensions”, and how to create a sales report with Employees (Dimension) with a slowly changing Attribute (Team). See how to create the correct grain for the Fact Table and Dimension Table using Power Query, and then see how to load it to the Excel Power Pivot Data Model and create Relationships, DAX Formula and a Data Model PivotTable Report that shows Teams and Employee and how their sales changing over time.
Topics:
1. (00:09) Introduction and Explanation of Problem with Slowly Changing Dimensions when creating Reports.
2. (02:00) Transform Two Dimension Tables into a Lookup Table that can be used in a Merge to bring Employee-Team Unique Identifier into Fact Table
3. (08:27) Build Employee Team Dimension Table
4. (08:45) Load to Data Model
5. (09:18) Build Relationships
6. (09:29) Build DAX Formula
7. (10:03)Build Data Model PivotTable Report
8. (11:06) Summary

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

View on YouTube

Unpivot Data in Excel Using Power Query (FASTEST way ever)

Posted on by Annie D

In this video, I will show you how to quickly unpivot data in Excel using Power Query.

Since the Pivot table needs a specific data format, in case you get the data in any other format, you need to change it to able to use it in pivot tables.

In this example, I show you a common format that you can easily unpivot in Power Query with a few clicks.

The best part about using Power Query is that it’s linked to the original data. So in case your original data expand or change, all you need to do is refresh the resulting data and it will automatically update.

This video is a part of my ‘Excel is two-minute’ series, where I share simple and quick tips to help you speed up your work and be more efficient

Subscribe to this YouTube channel to get updates on Excel Tips and Excel Tutorials videos – https://www.youtube.com/c/trumpexcel

This YouTube channel is managed by Sumit Bansal (who also runs the TrumpExcel website). This channel is meant for people who want to learn Excel. It covers a lot of Excel basics and advanced Excel topics such as Excel Formulas, Functions, Pivot Table, Shortcuts, Excel VBA, Macros, Excel Dashboards, Excel Charts, Conditional Formatting, Power Query, etc. Please subscribe to this channel to be the first to know when new Excel tutorials come out.

You can find a lot of useful Excel resources on the following site: https://trumpexcel.com/

#Excel #ExcelTips #Excelin2Minutes

View on YouTube

How to Create Custom Sort List in Excel (Easy Step-by-Step)

Posted on by Annie D

In this video, I will show you how to create your own custom sort list in Excel

While there are already some inbuilt criteria that you can use to sort in Excel (such as alphabetically, by numbers or dates), in some cases you may have a need to use a custom sort criteria.

For example, you may want to sort based on High, Medium, Low.

In such a case, you can create your own sorting criteria and then use this criterion while sorting the data.

This video is a part of my ‘Excel is two-minute’ series, where I share simple and quick tips to help you speed up your work and be more efficient

Subscribe to this YouTube channel to get updates on Excel Tips and Excel Tutorials videos – https://www.youtube.com/c/trumpexcel

This YouTube channel is managed by Sumit Bansal (who also runs the TrumpExcel website). This channel is meant for people who want to learn Excel.

It covers a lot of Excel basics and advanced Excel topics such as Excel Formulas, Functions, Pivot Table, Shortcuts, Excel VBA, Macros, Excel Dashboards, Excel Charts, Conditional Formatting, Power Query, etc. Please subscribe to this channel to be the first to know when new Excel tutorials come out.

You can find a lot of useful Excel resources on the following site: https://trumpexcel.com/

#Excel #ExcelTips #Excelin2Minutes

View on YouTube

Send MS Forms responses to email using Flow

Posted on by Annie D

In today’s video, I am going to show you how to get your MS Form responses sent to your email.
If you want to try it live, give some feedback on the dax fridays series here:

Here you can download all the pbix files: https://ift.tt/2yGx9Ih

SUBSCRIBE to learn more about Power and Excel BI!
https://www.youtube.com/channel/UCJ7UhloHSA4wAqPzyi6TOkw?sub_confirmation=1

Our PLAYLISTS:
– Join our DAX Fridays! Series: https://goo.gl/FtUWUX
– Power BI dashboards for beginners: https://goo.gl/9YzyDP
– Power BI Tips & Tricks: https://goo.gl/H6kUbP
– Power Bi and Google Analytics: https://goo.gl/ZNsY8l

☼☼☼☼☼☼☼☼☼☼

POWER BI COURSES:

Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:
https://ift.tt/2NEZM2b

☼☼☼☼☼☼☼☼☼☼

ABOUT CURBAL:
Website: http://www.curbal.com
Contact us: https://ift.tt/2qhiZvU

▼▼▼▼▼▼▼▼▼▼

If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:

https://ift.tt/2NrgChB

Many thanks in advance!

▲▲▲▲▲▲▲▲▲▲

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

What gear do I use to make my videos and run my business? Below you will find a list of most of my gear. The links on the store are affiliate links, meaning if you buy something from them, amazon will give a small commission and you will be supporting my channel indirectly. Thanks in advance!

https://ift.tt/2I8nl09

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

QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Linkedin ► https://goo.gl/3VW6Ky
Twitter ► @curbalen, @ruthpozuelo
Facebook ► https://goo.gl/bME2sB

#POWERBITIPS #CURBAL #POWERBI #MVP

View on YouTube

Excel Formula: Count Matches in Both Lists. Conditional Formatting to Format Matches. EMT 1613

Posted on by Annie D

Download Excel File: https://ift.tt/2BxapfF
Learn about how to create a Single Cell Formula to Count the Number of Matches In Two Lists with the MATCH and COUNT Functions. Also learn hos to add Conditional Formatting to highlight values that are in both lists.
Topics:
1. (00:01) Intro
2. (00:21) Single Cell Formula to Count the Number of Matches In Two Lists
3. (03:46) Conditional Formatting to Highlight Matches In Two Lists
4. (03:30) Summary

View on YouTube

Show Numbers in Thousands or Millions in Excel (really easy way)

Posted on by Annie D

In this video, I will show you a really easy way to show numbers in thousands in Excel (or in millions or billions).

You can do this using the custom number formatting where you can specify the format to show values in thousands (or millions) and also add the word “Thousand” or “K” to it.

The best part about this method is that it only changes how your data looks in Excel and has no impact on the value within the cell. So you can still use the cell in calculations.

This video is a part of my ‘Excel is two-minute’ series, where I share simple and quick tips to help you speed up your work and be more efficient

Subscribe to this YouTube channel to get updates on Excel Tips and Excel Tutorials videos – https://www.youtube.com/c/trumpexcel

This YouTube channel is managed by Sumit Bansal (who also runs the TrumpExcel website). This channel is meant for people who want to learn Excel.

It covers a lot of Excel basics and advanced Excel topics such as Excel Formulas, Functions, Pivot Table, Shortcuts, Excel VBA, Macros, Excel Dashboards, Excel Charts, Conditional Formatting, Power Query, etc. Please subscribe to this channel to be the first to know when new Excel tutorials come out.

You can find a lot of useful Excel resources on the following site: https://trumpexcel.com/

#Excel #ExcelTips #Excelin2Minutes

View on YouTube

One tip to work easier with JSON theme file in Power BI

Posted on by Annie D

Working with JSON files is not easy, at least not for me. In today’s video, I will show you one trick that will make it easier to work and discover JSON properties when creating Power BI theme files.

Get visual studio code:
https://ift.tt/2pHDJNP

Get the extension:
https://ift.tt/2MyGl9V
Here you can download all the pbix files: https://ift.tt/2yGx9Ih

SUBSCRIBE to learn more about Power and Excel BI!
https://www.youtube.com/channel/UCJ7UhloHSA4wAqPzyi6TOkw?sub_confirmation=1

Our PLAYLISTS:
– Join our DAX Fridays! Series: https://goo.gl/FtUWUX
– Power BI dashboards for beginners: https://goo.gl/9YzyDP
– Power BI Tips & Tricks: https://goo.gl/H6kUbP
– Power Bi and Google Analytics: https://goo.gl/ZNsY8l

☼☼☼☼☼☼☼☼☼☼

POWER BI COURSES:

Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:
https://ift.tt/2NEZM2b

☼☼☼☼☼☼☼☼☼☼

ABOUT CURBAL:
Website: http://www.curbal.com
Contact us: https://ift.tt/2qhiZvU

▼▼▼▼▼▼▼▼▼▼

If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:

https://ift.tt/2NrgChB

Many thanks in advance!

▲▲▲▲▲▲▲▲▲▲

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

What gear do I use to make my videos and run my business? Below you will find a list of most of my gear. The links on the store are affiliate links, meaning if you buy something from them, amazon will give a small commission and you will be supporting my channel indirectly. Thanks in advance!

https://ift.tt/2I8nl09

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

QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Linkedin ► https://goo.gl/3VW6Ky
Twitter ► @curbalen, @ruthpozuelo
Facebook ► https://goo.gl/bME2sB

#CURBAL #SUBSCRIBE

View on YouTube

MSPTDA 33 Part 2: Power Query & Standard PivotTable To Show All Employees Sales Or Not

Posted on by Annie D

Download Start Excel file: https://ift.tt/2MwVY1F
Download Finished Excel file: https://ift.tt/2MCjWZv
Pdf files with notes: https://ift.tt/2IOoqtJ

This is an alternative method for what we did last video, where we need a report to show all Teams and All Employees regardless of whether or not they had sales. This shows how to deal with a Slowly Changing Dimension where the employees on Teams change over time. See Power Query M Code from Bill Szysz.
Topics:
1. (00:09) Introduction to Data Setup and Goal of Power Query Data Modeling
2. (01:46) Power Query Transformation for Dimension Table into a Fact Table. Including List Syntax to create a list of dates that we can use to expand and repeat the record for each date in the period.
3. (06:06) Build Standard PivotTable Report, including Loading Power Query Output directly to a Standard PivotTable.
4. (06:35) Summary

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

Excelisfun, Highline College, Data Analysis, Business Intelligence, BI 348, Mike Girvin, Microsoft Power Tools for Data Analysis 32, MSPTDA 32, MSPTDA 33, MSPTDA 34, MSPTDA 35, Slowly Changing Dimensions, Data modeling Slowly Changing Dimension, Power Query Data Modeling, Power Query M Code, Standard PivotTable, List Syntax, Dates as Whole Numbers, List Syntax Trick for List of Dates, Bill Szysz, Power Query Transformation

View on YouTube