Month: July 2019

Add index to subgroups in Power Query

Posted on by Annie D

In today’s video, I am going to show you how to add individual indexes to groups in power query using the M language. #powerquery #powerbi #curbal

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

View on YouTube

Conditional Formatting For Chart Visuals In Power BI – What’s Possible?

Posted on by Annie D

In this tutorial I wanted to showcase to you some examples of how you can create custom conditional formatting within charts and visuals.

This is a seriously good addition to Power BI functionality, and I’m hoping to give you some inspiration here.

There are almost unlimited ways you can create insights within your visuals no using this feature.

A great video to work through. Enjoy.

Sam

***** Learning Power BI? *****

FREE COURSE – Ultimate Beginners Guide To Power BI – https://ift.tt/2Lmxubn

FREE COURSE – Ultimate Beginners Guide To DAX – https://ift.tt/2IJLvOv

FREE – Power BI Resources – https://ift.tt/2LiAVQj

FREE – 60 Page DAX Reference Guide Download – https://ift.tt/2IJLx95

Learn more about Enterprise DNA – https://ift.tt/2LiAWDR

Enterprise DNA Membership – https://ift.tt/2IJLxWD

View on YouTube

Excel’s # 1 Silliest Formula: INDEX or INDIRECT? Excel Magic Trick 1571 (Two Way Lookup Trick)

Posted on by Annie D

Download Excel Start File: https://ift.tt/30JEza7
Download Excel Finished File; https://ift.tt/2xW8n7g
In this video have fun with silly and inefficient formulas to do a Two-Way Lookup using: 1) the INDEXT and MATCH Functions and the Space Operator and 2) the Defined Name Feature, The INDIRECT Functions and the Space Operator. Both formulas are for fun because there are more efficient formulas as seen in these videos:
Excel Two Way Lookup with VLOOKUP & MATCH Functions – Excel Magic Trick 1567

Excel Two Way Lookup with INDEX & MATCH Functions – Excel Magic Trick 1568
https://www.youtube.com/watch?v=TaLc6ZToY3Q

View on YouTube

Excel Union Operator For Aggregate, Statistical & Finance Functions – Excel Magic Trick 1569

Posted on by Annie D

Download Excel File: https://ift.tt/30DK5uV
In this video learn about how to use the Union Operator in standard Aggregate functions, like SUM and AVERAGE and how to use it inside of parentheses inside Statistical and Finance Functions.
Related Videos:
Excel Union Operator For Aggregate, Statistical & Finance Functions – Excel Magic Trick 1569
Excel Intersection Operator, An Excel Party Trick – Excel Magic Trick 1570

View on YouTube

Excel Intersection Operator, An Excel Party Trick? – Excel Magic Trick 1570

Posted on by Annie D

Download Excel File: https://ift.tt/30DK5uV
In this video learn how to use the Intersection Operator, which is a space. Learn about the #NULL Error.
Related Videos:
Excel Union Operator For Aggregate, Statistical & Finance Functions – Excel Magic Trick 1569
Excel Intersection Operator, An Excel Party Trick – Excel Magic Trick 1570

View on YouTube

DAX Fridays! #133: PERCENTILE.EXC and PERCENTILE.INC

Posted on by Annie D

In today’s video I will explain how to calculate quartiles in power bi and why they are useful.
Happy summer! #curbal #powerbi #dax #daxfridays

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: 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 #dax #powerbi #mvp

View on YouTube

DAX Fridays! #133: PERCENTILE.EXC and PERCENTILE.INC

Posted on by Annie D

In today’s video I will explain how to calculate quartiles in power bi and why they are useful.
Happy summer! #curbal #powerbi #dax #daxfridays

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: 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 #dax #powerbi #mvp

View on YouTube

DAX Fridays! #133: PERCENTILE.EXC and PERCENTILE.INC

Posted on by Annie D

In today’s video I will explain how to calculate quartiles in power bi and why they are useful.
Happy summer! #curbal #powerbi #dax #daxfridays

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: 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 #dax #powerbi #mvp

View on YouTube

Highlight Alternate Rows (or Every Third/Fourth Row) in Excel – Using Formula

Posted on by Annie D

In this video, I will show you how to highlight alternate rows in Excel (or how to highlight/color every second, third, or fourth row in Excel).

The trick is to use Conditional formatting with a formula that checks each cell for the row number and returns a TRUE for every second row and FALSE otherwise.

This TRUE/FALSE result is then used by Conditional Formatting to apply the specified formatting.

In this video, I use the MOD function to check the row number (which is given by the ROW function).

The formula used is =MOD(ROW(),2)=1

The above formula takes the ROW function result and divides it by 2 and returns the remainder. The possible result of the MOD function in this can be a 0 or a 1 (0 if the row number is even and 1 when it’s odd).

It then checks whether the value is equal to 1 or not. Since the data starts from second row onwards, I am checking for 1. else I would have checked for 0.

This formula then returns a TRUE when the ROW number is either 3 or 5 or 7 or so on, and FALSE when it’s even.

Conditional formatting then uses this to shade/color every second row in the dataset.

In case you want to highlight/color every third row, you change the formula to =MOD(ROW(),3)=1

This would highlight every third row instead.

Another quick way to highlight alternate rows in Excel is to convert the data into an Excel Table. An Excel Table automatically colors alternate rows.

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 #ExcelTutorial

View on YouTube

How To Use Virtual Tables With COUNTROWS in Power BI – DAX Formula Technique

Posted on by Annie D

In this example I want to expand you mind around the possibilities you have when using the COUNTROWS functions.

You are not just restricted to using COUNTROWS with a physical table. You can also use virtual tables that contain complex logic themselves.

This is a very good review of virtual tables in general and the flexibility you have in Power BI when you use them effectively.

Good luck

Sam

***** Learning Power BI? *****

FREE COURSE – Ultimate Beginners Guide To Power BI – https://ift.tt/2Lmxubn

FREE COURSE – Ultimate Beginners Guide To DAX – https://ift.tt/2IJLvOv

FREE – Power BI Resources – https://ift.tt/2LiAVQj

FREE – 60 Page DAX Reference Guide Download – https://ift.tt/2IJLx95

Learn more about Enterprise DNA – https://ift.tt/2LiAWDR

Enterprise DNA Membership – https://ift.tt/2IJLxWD

View on YouTube

Excel Two Way Lookup with INDEX & MATCH Functions – Excel Magic Trick 1568

Posted on by Annie D

Download Excel File: https://ift.tt/30DK5uV
In this video learn how to do a Two Way Lookup from a Rectangular Lookup Table using the INDEX and MATCH functions. In our example we need to lookup a product name in the row and a month name in the column and return the intersecting value.
Reference Videos:
Excel Two Way Lookup with VLOOKUP & MATCH Functions – Excel Magic Trick 1567
Excel Two Way Lookup with INDEX & MATCH Functions – Excel Magic Trick 1568

View on YouTube

Excel Two Way Lookup with VLOOKUP & MATCH Functions – Excel Magic Trick 1567

Posted on by Annie D

Download Excel File: https://ift.tt/30DK5uV
In this video learn how to do a Two Way Lookup from a Rectangular Lookup Table using the VLOOKUP and MATCH functions. In our example we need to lookup a product name in the row and a month name in the column and return the intersecting value.
Reference Videos:
Excel Two Way Lookup with VLOOKUP & MATCH Functions – Excel Magic Trick 1567
Excel Two Way Lookup with INDEX & MATCH Functions – Excel Magic Trick 1568

View on YouTube