Tag: How to

[FREE Excel Course] Lesson 19 – Data Sorting & Filtering (Basic + Advanced))

Posted on by Annie D

In this video, you will learn about data sorting and filtering in Excel. It covers the following topics:

— Data Sorting
— Multi-Levels of Sorting
— Data Filtering
— Advanced Filtering
— Filtering and Sorting in Excel Data Tables

Excel has many data analysis functionalities and a good knowledge of filtering and sorting data in Excel will help you analyze a huge amount of data easily.

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

KPI visuals, line charts, filter pane updates on May 2019 Power BI Desktop Update

Posted on by Annie D

In this video I will review the new reporting features released on the Power BI desktop Update for May, and take the opportunity to explaing in more detail how the KPI visual works.
#powerweek #curbal #powerbi

Keynotes:
1. Reorder filter pane in power bi
2. change the position of the legends in the line chart
3. Change the text size of the KPI visual and explain how it works

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

▲▲▲▲▲▲▲▲▲▲

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

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

#POWERWEEK #CURBAL #POWERBI #POWERBIUPDATE #MVP

View on YouTube

Excel Magic Trick 1551: Lookup Discounted Price? 2 VLOOKUPs & ROUND

Posted on by Annie D

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

In this video learn how to to lookup a discounted price based on Product Name and Units Discount. See how to use 2 VLOOKUP Functions & the ROUND Function in a single formula.

View on YouTube

[FREE Excel Course] Lesson 18 – Data Validation in Excel (drop-down lists)

Posted on by Annie D

In this video, you will learn about using data validation and drop-down lists.

It covers the following topics:
— Data Validation Criteria
— Input Message
— Customizing Error Messages
— Circle Invalid Entries
— Advanced Data Validation Tricks (such as Create Dependent Validation, Make Sub Headings in Validation, Disguise Numbers as Text, Creating Dynamic Drop Down List)

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

MSPTDA 24: DAX Multiply / Divide Data Types Discrepancies Solved!

Posted on by Annie D

Download files:
Start File: http://bit.ly/2w4SgDh
Finished File: http://bit.ly/2Q6ESYH
pdf notes for Video #24: http://bit.ly/2w7crRe

In this video learn about how different DAX Data Types can affect multiplication and division results and yield different answers depending on the order of the multiplication or division. This apparently violates the Commutative Property of Multiplication.
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! #127: Calculate Same period last Month in DAX

Posted on by Annie D

Comparing periods is something you should always do as it adds context to your data, but how do
you calculate Same period last month? PREVIOUSMONTH function will give you the entire previous month, so how do you do it?
#powerbi #curbal #dax #daxfridays

Here is one of the many ways to do it.

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 23: Two Fact Tables? DAX, Power Query or Worksheet Formulas to Convert to 1 Fact Table

Posted on by Annie D

All 9 files for video in this zipped folder: http://bit.ly/2JGTGfd
Download file individually at class web site: http://bit.ly/2GF25Ni
pfd notes for Video #23: http://bit.ly/2JGaCTb

In this video learn about how to convert 2 Fact Tables into one with DAX, Worksheet Formulas or Power Query
Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.
Topics:
1. (00:16) Introduction

2. (02:35) Excel Worksheet Formula Solution
3. (05:01) How do we allocate Discount from Invoice Grain to Invoice Line Grain?
4. (05:45) Worksheet Formula for Total Invoice Sales at Invoice Grain using SUMPRODUCT function
5. (09:23) Worksheet Formula for % Sales Discount at Invoice Grain using division
6. (10:00) Worksheet Formula for Line Discount at Invoice Line Grain using VLOOKUP and multiplication
7. (12:12) How do we allocate Shipping from Invoice Grain to Invoice Line Grain?
8. (13:27) Worksheet Formula for Invoice Line Shipping Weight at Invoice Line Grain using VLOOKUP and multiplication
9. (14:30) Worksheet Formula for Invoice Weight at Invoice Grain using SUMIFS
10. (15:04) Worksheet Formula for Line Shipping at Invoice Line Grain using VLOOKUP and multiplication
11. (16:50) Create Excel Reports at Product Grain.
12. (17:03) Standard PivotTable Report
13. (17:53) Worksheet Formula Report

14. (21:26) DAX Formula Solution in Power Pivot
15. (22:30) Look at Data Model and preview of DAX Formulas and functions SUMX, RELATED and RELATEDTABLE
16. (24:30) Bring Excel Tables into Data Model
17. (25:15) Create Relationships between tables
18. (26:23) How to Allocate Invoice Grain Numbers to Invoice Line Grain Numbers
19. (26:56) DAX Formula for Total Invoice Sales at Invoice Grain using SUMX and RELATEDTABLE functions
20. (29:40) DAX Formula for % Sales Discount at Invoice Grain using DIVIDE function
21. (30:50) DAX Formula for Line Discount at Invoice Line Grain using RELATED function and multiplication
22. (31:57) DAX Measure for Total Discount
23. (32:39) Data Model PivotTable Report for Product Discount
24. (33:08) How do we allocate Shipping from Invoice Grain to Invoice Line Grain?+
25. (34:22) DAX Formula for Invoice Weight at Invoice Grain using SUMX, RELATEDTABLE and RELATED.
26. (35:56) Visuals to understand how DAX Formula with SUMX, REALTEDTABLE and RELATED are working to traverse multiple relationships in one formula. This helps illustrates the Power of DAX for Business Calculations.
27. (36:52) DAX Formula for Line Shipping at Invoice Line Grain using RELATED and multiplication and division. Three RELATED function in one formula
28. (39:03) DAX Measure for Total Shipping
29. (39:25) Final Data Model PivotTable

30. (40:22) Power Query Solution in Power BI Desktop
31. (41:09) Why Two Fact Tables will not work with all Dimension Tables for Reporting.
32. (43:05) Summary and visuals of steps we need to perform
33. (44:12) Create blank Power BI Desktop file
34. (44:40) Import Two Fact Table Data Model from Power Pivot
35. (45:40) Power Query Formula to calculate Sales at Invoice Line Grain using Table.AddColumn function
36. (46:53) Power Query Merge to lookup Product Weight at Invoice Line Grain
37. (47:26) Power Query Formula to calculate Product Shipping Weight at Invoice Line Grain using Table.AddColumn function
38. (47:42) Power Query Group By feature to aggregate Invoice Sales, Invoice Shipping Weight and all rows in Invoice Line Grain Table for each Invoice Number.
39. (49:52) Power Query Merge to pull Invoice Grain Shipping & Discount numbers, as well as to pull the Invoice Level Dimensions of Date and Sales Rep ID into the current step in the query (later after expanding it will be the Invoice Line Grain).
40. (50:52) Power Query Formula for % Sales Discount at Invoice Grain using Table.AddColumn function
41. (52:11) Expand to get back to Invoice Line Grain
42. (52:31) Note about Unit Price and how it is stored as a Fact because it changes so often.
43. (54:16) Power Query Formula for Line Discount at Invoice Line Grain using Table.AddColumn function and Number.Round
44. (54:49) Power Query Formula for Line Shipping at Invoice Line Grain using Custom Column using Table.AddColumn function and Number.Round
45. (57:23) Remove all column we do not need in final Fact Table
46. (58:08) Load Tables to Data Model, except Invoice Level Table.
47. (59:15) Create DAX Measures for Shipping, Discounts and Sales
48. (01:00:10) Create % DAX Measures for Shipping and Discount as a percent of sales. Use the DIVIDE DAX Function.
49. (01:01:15) Hide Columns from Report View
50. (01:01:33) Look at Final Data Model
51. (01:01:47) Create Visualization in Power BI Desktop
52. (01:04:40) Summary

View on YouTube

rank in Access

5 Lifesaving ⛑ tips in Power Bi you might not know

Posted on by Annie D

You know how this works by now right? If I learn something useful, I share it here with you and in today’s video I am going to share my top 5 power bi tips to fix current issues and cool functionality.

Did I manage to surprise you with a tip you didnt know? #curbal #powerbi #5tips

Keynotes:
1. Word wrap, mini map and more power query
2. Copy formatting in a visual
3. Fix non-responsive/ freeze power bi ribbon
4. Bookmark filter pane status
5. Copy buttons with actions.

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 17 – Named Ranges in Excel

Posted on by Annie D

In this video of this online Excel Training, you will learn about using Named Ranges in Excel.

It covers the following topics:

— Creating Named Ranges
— Managing Named Ranges
— Important Keyboard Shortcuts
— Creating Dynamic Named Ranges using OFFSET and INDEX formulas

This video is a part of my Free Excel course 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:

TrumpExcel.com | Online Excel Tips & Tutorials (with videos)


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

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

How to Merge Excel Files with WBMerge

Posted on by Annie D

To buy or try WBMerge: http://bit.ly/2WH82Qt
Save 20% on WBMerge through June 30 2019 using discount code MREXCEL

In this video, I try the WBMerge utility on a collection of Excel workbooks that might be problematic in Power Query.

WBMerge is a $39 utility from Workbook Tools. The utility can merge data from multiple Excel workbook or multiple Google Sheets files.

View on YouTube

Export one table of data or the entire model with DAX Studio

Posted on by Annie D

In today’s video I am going to show you how to export data from one table or the entire model using DAX Studio.
#curbal #dax #daxstudio

Keynote:
02:00 Download data from one table with DAX Studio
02:40 Download the entire model with DAX Studio

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

DAX Fridays! #126: Understand the Formula Engine and Storage Engine to optimize your DAX queries

Posted on by Annie D

To be able to optimize DAX queries, you need to know how the formula engine and the storage engine work with them, so how about we let them explain themselves?
#curbal #powerbi #dax #DAXFridays

Hope you enjoy the video, i had fun doing it 🙂

Have you missed the Vertipaq series? Here are the videos:
Part1: In this video, I will go through the main known things that Vertipaq does to compress the data: https://www.youtube.com/watch?v=b8oSgTOeYCE

Part2: In this video, we will go through what it means that Vertipaq is a columnar database and why performance is so good: https://www.youtube.com/watch?v=A461ktvExH0

Part3: Why is Vertipaq so fast? Segmenting the data is one of Vertipaq tricks to manage data at blazing fast speeds. In this video, I will explain what that is: https://www.youtube.com/watch?v=A67tsJ03Afs

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