Tag: How to

Connect directly to Power BI service and analyze that data in Excel

Posted on by Annie D

Did you know that you connect to a dataset in the Power BI service using Excel as you do today with Power BI Desktop? No? Then watch this video, it is going to blow you away!!

#powerbi #excel #curbal

Here are the links you need to get this started!!
http://bit.ly/2H9sUdH
http://bit.ly/1RJgKWK

XMLA video: (more to come)

Here you can download all the pbix files: http://bit.ly/2PUURaz

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:
http://bit.ly/2V15a0p

☼☼☼☼☼☼☼☼☼☼

ABOUT CURBAL:
Website: http://www.curbal.com
Contact us: http://bit.ly/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:

http://bit.ly/2V3o0Ux

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://amzn.to/2YRfIBm

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

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

[Free Excel Course] Lesson 25 – Protecting Worksheets / Workbooks in Excel

Posted on by Annie D

In this video of this Free training, I will show you how you can lock cells or protect worksheets and workbooks in Excel.

You can find more details about this Free Excel Training + the download files here: http://bit.ly/2snelIX

You can protect cells, worksheets, and workbooks in Excel with a password (or without one). Once a worksheet is protected, you’ll need to enter the password to access it (in case a password has been applied).

Note that Excel in-built security feature is not very strong and you can easily open worksheets/workbooks that have been locked. However, if you want to quickly protect it before sending it to your manager/client (so they don’t end up changing anything by mistake), it works well.

This video is a part of my Free Online Excel Training where I show you how to use Excel from the basics and also cover a lot of advanced topics.

You can also access the Full Excel Training on YouTube here: https://www.youtube.com/watch?v=UPABcYhugZk&list=PLm8I8moAHiH2kkq0S9XGvIbPODrHzXRp6

-~-~~-~~~-~~-~-
Find Amazing Excel Tips & Tutorials:

FREE Online Excel Training – Video Course for Beginners


-~-~~-~~~-~~-~-

Let’s Connect:
Google+ ► http://bit.ly/2jWiOzU
Facebook ► http://bit.ly/2j9gyrO
Twitter ► https://twitter.com/TrumpExcel
Pinterest ► http://bit.ly/2jWm2mZ
Youtube Channel ► https://www.youtube.com/c/trumpexcel

View on YouTube

MSPTDA 28: Build Power Query Bridge Table in Power BI & Power Pivot for Many To Many Relationship

Posted on by Annie D

Download files for videos: http://bit.ly/2KhPz9Z
Class web site with individual file downloads: http://bit.ly/2GF25Ni
pdf notes for Video: http://bit.ly/2KicfXm

In this video learn about how to create a Bridge Table for a Many-To-Many Relationship using Power Query in either Power BI or Excel Power Pivot. Learn three methods for a Bridge Table: B-Directional Filetrs, CROSSFILTER DAX Function or a Table Filter.
Topics:
1. (00:23) Introduction & look at Data Modeling Problem
2. (01:00) Fundamental Problem with a Many-To-Many Relationship
3. (02:05) What is a Many-To-Many Relationship?
4. (03:25) Start to create Reports and discover the Problem with a Many-To-Many Relationship.
5. (04:52) Build Bridge Table using Excel Power Query
6. (07:32) First look at underlying filtering for a Many-To-Many Relationships
7. (08:36) Three Options with a Bridge Table.
8. (09:02) Build Bridge Table using Power BI Desktop Power Query
9. (10:25) Bi-Directional Filter
10. (10:53) Visual Animation to illustrate how Many-To-Many Relationship works with a Bridge Table.
11. (11:39) Ambiguity in Model with Bi-Directional Filters.
12. (12:19) CROSSFILTER DAX Function example
13. (13:41) Table Filter Example
14. (14:05) Expanded Diagram to “see” how Table Filter works with a Bridge Table and a Many-To-Many Relationships
15. (15:08) Difference between Grand Total Cell formulas for CROSSFILTER and Table Filter
16. (16:10) Cross Tab Report
17. (16:53) Conclusion
Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.

View on YouTube

MSPTDA 27: SUMPRODUCT & SUMIFS Worksheet Array Formula to Add Units in Many To Many Relationships

Posted on by Annie D

Download files for videos: http://bit.ly/2KhPz9Z
Class web site with individual file downloads: http://bit.ly/2GF25Ni
pdf notes for Video: http://bit.ly/2KicfXm

In this video learn about how add total units from a Fact Table with Book ID. See two formulas: Total Units for each Book ID, but then also Total Units for Each Author when each book may have multiple Authors, in a Many-To-Many Relationship. Learn about the Plus Math Operator for OR Logical Test. See the IF Function to lookup correct Book IDs based on OR Logical Test.
Topics:
1. (00:15) Introduction
2. (01:12) Many-To-Many Relationships. What is a Many To Many Relationship?
3. (02:34) Worksheet Formulas and caveats
4. (03:22) SUMIFS, INDEX and MATCH Worksheet Functions to create a Total Units for Book ID Report.
5. (06:01) SUMIFS, INDEX, MATCH, IF and SUMIFS Worksheet Functions in an OR Logical Test Calculation to help simulate a Many-To-Many Relationship with the goal of creating to create a Total Units for each Author Report.
6. (08:05) OR Logical Test
7. (12:52) Conclusion
Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.

View on YouTube

DAX Fridays! #129: New functionality for ALLSELECTED

Posted on by Annie D

In the Power BI desktop update for May 2019, the ALLSELECTED fucntion functionality has been updated and in this video we discuss the changes.

The previous functionality was discussed here:
http://bit.ly/2KeNcEQ

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

Link to EXCEL SURVEY: https://1drv.ms/xs/s!Ar8CDNp8cGTcgjaHonN82T8I1jQT

Here you can download all the pbix files: http://bit.ly/2PUURaz

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:
http://bit.ly/2V15a0p

☼☼☼☼☼☼☼☼☼☼

ABOUT CURBAL:
Website: http://www.curbal.com
Contact us: http://bit.ly/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:

http://bit.ly/2V3o0Ux

Many thanks in advance!Here you can download all the pbix files: http://bit.ly/2PUURaz

▲▲▲▲▲▲▲▲▲▲

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://amzn.to/2YRfIBm

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

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

#daxfridays #curbal #dax #powerbi #mvp

View on YouTube

[Free Excel Course] Lesson 24 – Excel Pivot Table Overview

Posted on by Annie D

In this video of this Free Excel training, I will show you how to create and use Pivot Tables in Excel.

The video covers the following topics:

— Introduction to Pivot Tables
— Creating a Pivot Table
— Formatting Pivot Table
— Grouping data in Excel Pivot table
— Calculated Field/Items
–Slicers
— Pivot Charts

Pivot Tables are amazing as it allows you to quickly summarize a huge amount of data with a few clicks. Even if you’re using Excel for the first time, you can quickly get a hang of it.

In this video, I try to cover a lot about the Pivot Table. In case you want to learn more, check out all my Pivot Table tutorials here: http://bit.ly/2EI6Wgr

This video is a part of my Free Online Excel Training where I show you how to use Excel from the basics and also cover a lot of advanced topics.

You can find more details about this Free Excel Training + the download files here: http://bit.ly/2snelIX

You can also access the Full Excel Training on YouTube here: https://www.youtube.com/watch?v=UPABcYhugZk&list=PLm8I8moAHiH2kkq0S9XGvIbPODrHzXRp6

-~-~~-~~~-~~-~-
Find Amazing Excel Tips & Tutorials:

FREE Online Excel Training – Video Course for Beginners


-~-~~-~~~-~~-~-

Let’s Connect:
Google+ ► http://bit.ly/2jWiOzU
Facebook ► http://bit.ly/2j9gyrO
Twitter ► https://twitter.com/TrumpExcel
Pinterest ► http://bit.ly/2jWm2mZ
Youtube Channel ► https://www.youtube.com/c/trumpexcel

View on YouTube

In the head of: Marc and Dave, the Power BI cheatsheet authors

Posted on by Annie D

During SQL Saturday in Stockholm, i had a chance to meet Dave and Marc, the authors of the Power BI Cheat Sheet and had a chat with them. Thanks guys!
#powerbi #cheetsheet #curbal

Download the Power Bi cheatsheet here: http://bit.ly/2EFUPR0

and here is the merch for charity:
http://bit.ly/2XciHmt

Here you can download all the pbix files: http://bit.ly/2PUURaz

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:
http://bit.ly/2V15a0p

☼☼☼☼☼☼☼☼☼☼

ABOUT CURBAL:
Website: http://www.curbal.com
Contact us: http://bit.ly/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:

http://bit.ly/2V3o0Ux

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://amzn.to/2YRfIBm

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

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

View on YouTube

Average Last 3 Customer Sales: Power Query, DAX Measure or Worksheet Formula

Posted on by Annie D

Download Excel Finished Files: http://bit.ly/2K9SNfn
Entire page with all Excel Files for All Videos: http://bit.ly/1kSFWvs

In this video learn how to Average a customers last three sales (last three dates) using Excel Worksheet Formulas, Power Query M Code and a Power Pivot & Power BI DAX Measure.
Topics:
1. (00:07) Introduction
2. (00:50) Sort Method
3. (02:09) Excel Worksheet Formulas, including AGGREGATE and AVERAGEIFS functions.
4. (05:27) LARGE Function and New Excel Office 365 Calculation Engine
5. (08:15) Power Query M Code, including Table.Sort, List.Average and List.FirstN M Code functions
6. (12:46) Power Query Parameter Query with condition coming from Excel Worksheet
7. (14:05) Power Pivot & Power BI DAX Measure, including TOPN, CALCULATE and AVERAGE DAX Functions
8. (17:08) DAX Parameter from Excel Worksheet using VALUES DAX Function.
9. (18:43) Add new records to table and test formulas
10. (19:00) Fix Power Query Table.Sort bug using Dummy Insert Column
11. (20:08) Fix Power Query Table.Sort bug using Table.Sort function inside Table.Group Function
12. (22:12) Conclusion

View on YouTube

[Free Excel Course] Lesson 23 – Creating Dynamic Charts in Excel

Posted on by Annie D

In this video of this Free Excel training, I will show you how to create dynamic charts in Excel.

Dynamic charts allow you to change the chart based on user selection. For example, you can update the chart as soon as you change the scroll bar or click a button or make a selection from a drop-down list

This video covers two detailed examples of creating dynamic charts from scratch

Example 1 – Creating a dynamic chart that updates as soon as you use the scroll bar to change the year value.

Example 2 – Creating a dynamic chart that plots the data on a scatter chart based on the column you select. There is a drop-down that allows you to select which data series should be plotted on the X-axis and which should be plotted on the Y Axis

This video is a part of my Free Online Excel Training where I show you how to use Excel from the basics and also cover a lot of advanced topics.

You can find more details about this Free Excel Training + the download files here: http://bit.ly/2snelIX

You can also access the Full Excel Training on YouTube here: https://www.youtube.com/watch?v=UPABcYhugZk&list=PLm8I8moAHiH2kkq0S9XGvIbPODrHzXRp6

-~-~~-~~~-~~-~-
Find Amazing Excel Tips & Tutorials:

FREE Online Excel Training – Video Course for Beginners


-~-~~-~~~-~~-~-

Let’s Connect:
Google+ ► http://bit.ly/2jWiOzU
Facebook ► http://bit.ly/2j9gyrO
Twitter ► https://twitter.com/TrumpExcel
Pinterest ► http://bit.ly/2jWm2mZ
Youtube Channel ► https://www.youtube.com/c/trumpexcel

View on YouTube

Power BI file to analyze json file from the Performance Analyzer (part 2)

Posted on by Annie D

In today’s video, I am going to share a power bi file that you can use to analyze the json file that you get as an output from the Power BI Performance Analyzer.
#curbal #powerbi

To get the file, go to curbal.com – Download center and Membership downloads.

Here you can download all the pbix files: http://bit.ly/2PUURaz

Video on Part 1: https://youtu.be/UCioY5ZgBkg

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:
http://bit.ly/2V15a0p

☼☼☼☼☼☼☼☼☼☼

ABOUT CURBAL:
Website: http://www.curbal.com
Contact us: http://bit.ly/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:

http://bit.ly/2V3o0Ux

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://amzn.to/2YRfIBm

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

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

View on YouTube

DAX Fridays! #128: Highlight MAX and MIN values in Power BI

Posted on by Annie D

In todays video I will show you how you can reuse conditional formatting using DAX and how to calculate max and min values of a series.

#curbal #daxfridays #dax #powerbi

Keynotes:
1. Calculate Min and Max value of a series
2. Format min and max values
3. Reuse conditional formatting using DAX

Link to relevant resources:
SWITCH fucntion: https://www.youtube.com/watch?v=-ykkaAtlCMc
MAXX function: https://www.youtube.com/watch?v=_FuL3kYoF0o
ALLSELECTED function: https://www.youtube.com/watch?v=n_7Telypk2Q
Deep five on variables: https://www.youtube.com/watch?v=z6VFrGWzDQ8

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

Link to EXCEL SURVEY: https://1drv.ms/xs/s!Ar8CDNp8cGTcgjaHonN82T8I1jQT

Here you can download all the pbix files: http://bit.ly/2PUURaz

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:
http://bit.ly/2V15a0p

☼☼☼☼☼☼☼☼☼☼

ABOUT CURBAL:
Website: http://www.curbal.com
Contact us: http://bit.ly/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:

http://bit.ly/2V3o0Ux

Many thanks in advance!Here you can download all the pbix files: http://bit.ly/2PUURaz

▲▲▲▲▲▲▲▲▲▲

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://amzn.to/2YRfIBm

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

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

#daxfridays #curbal #dax #powerbi #mvp

View on YouTube

MSPTDA 26: Budget vs Actual in Power Pivot & Power BI Desktop, DAX & Data Model

Posted on by Annie D

Download files for videos #26 & 26: http://bit.ly/2QgUgll
Class web site with individual file downloads: http://bit.ly/2GF25Ni
pdf notes for Video #25-26: http://bit.ly/2QgUj0v

In this video learn about how create a Data Model with Relationships and DAX Measures to compare month product budgeted amounts to actual amounts. Then calculate variances.
Topics:
1. (00:24) Introduction & look at Data Modeling Problem
2. (03:20) Data Modeling with Relationships
3. (05:15) Diagram and pictures of how Filter Context works in our Data Model
4. (07:00) DAX Measures
5. (08:35) Important Data Modeling with Hiding Columns
6. (09:10) Data Model PivotTable
7. (09:50) Attributes that can NOT slice our Report
8. (10:28) Conclusion
Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.

View on YouTube