Tag: How to

Power Query – Group Suppliers, Join with Line Feed From Multiple Text Files – Excel Magic Trick 1580

Posted on by Annie D

Download Excel File: https://ift.tt/2LMS71h
Download Text Files: https://ift.tt/2ypC4Oo
In this video we create a Material Supplier Report with All Suppliers Listed in Single Cell With Line Feed. We use a combination of Power Query Built-in featured like Group By and Merge Columns, and then we edit the M Code and use the Power Query Functions: Table.AddIndexColumn, Combiner.CombineTextByEachDelimiter, List.Transform, Text.From and Text.Combine. We also learn about a Line Feed in Power Query (Line Feed in M Code): #(lf) and how to use multiple delimiters in list syntax when merging or joining columns in Power Query.

Related Videos:
Count, Join then Lookup Suppliers & List in Single Cell With Line Feed, Excel Magic Trick 1579
Unique Invoice / Vendor List with Items Listed Horizontally – Excel Dynamic Array Formula- EMT 1575
Power Query M Code Group By Formula to Transform Invoice Table – Excel Magic Trick 1576

View on YouTube

Array Formula – List All Suppliers by Material in Single Cell with Line Feed, Excel Magic Trick 1579

Posted on by Annie D

Download Excel File: https://ift.tt/2LMS71h
In this video we create a Material Supplier Report with All Suppliers Listed in Single Cell With Line Feed. First we joining three columns with Supplier Data, Then create a Running Count for each Material, then use the Office 365 Dynamic Array Functions UNIQUE & FILTER to create the report. Also see the Functions: COUNTIFS, TEXTJOIN, and CHAR.

Related Videos:
Power Query – List All Suppliers by Material in Single Cell with Line Feed, Excel Magic Trick 1580
Count, Join then Lookup Suppliers & List in Single Cell With Line Feed, Excel Magic Trick 1579
Unique Invoice / Vendor List with Items Listed Horizontally – Excel Dynamic Array Formula- EMT 1575
Power Query M Code Group By Formula to Transform Invoice Table – Excel Magic Trick 1576

View on YouTube

Quickly change data types with M language in Power Query

Posted on by Annie D

In today’s video, I am going to show you how to quickly change data types with M language in Power Query. #curbal #powerbi #powerquery

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

Excel PivotTable Keyboard? Keyboard for PivotTable –Excel Magic Trick 1578

Posted on by Annie D

Download Excel File: https://ift.tt/2ZgL4Rp
In this video learn how to use a keyboard top create a PivotTable on the current Sheet.

PivotTable Keyboard = Alt, N, V
In Excel 2013 to 2007 Keyboard = Alt, N, V, T

View on YouTube

Using The Performance Analyzer In Power BI – Model Optimization Tips

Posted on by Annie D

In this tutorail we are going to review the new Performance Analyzer feature in Power BI.

We will first review how it works and some of it’s functionality, then I will run through a few optimization ideas for your reports and formulas that should improve performance.

This is a fantastic new addition to Power BI, that will help if your models and calculations are running slow.

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

Power Query M Code Group By Formula to Transform Invoice Table – Excel Magic Trick 1576

Posted on by Annie D

Download Excel File: https://ift.tt/32WVp7l
In this video learn how to take an invoice table, list the unique combinations of Invoice & Vendor and then list the items (references) horizontally. Learn how to use Power Query to create a solution. Learn how to edit the M Code in the Power Query Solution. Learn about the M Code Power Query Functions: List.Distinct, List.Transfrom, Table.Group and Text.Combine.

Related Video: Unique Invoice / Vendor List with Items Listed Horizontally – Excel Dynamic Array Formula- EMT 1575

View on YouTube

DAX Fridays! #134: CONFIDENCE.T and CONFIDENCE.NORM, confidence intervals with DAX

Posted on by Annie D

In today’s video I will explain how to calculate confidence intervals 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

How To Use The GROUPBY Function In Power BI – DAX Function Review

Posted on by Annie D

In this tutorial we will run through how to use the GROUPBY function in Power BI.

This is a similar table function to the likes of SUMMARIZE and SUMMARIZECOLUMNS, but it has some unique nuances to it that are interesting and worth diving into more deeply.

You can use the GROUPBY function in a few ways and I will look to show these to you during this particular video.

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

Unique Invoice / Vendor List with Items Listed Horizontally – Excel Dynamic Array Formula- EMT 1575

Posted on by Annie D

Download Excel File: https://ift.tt/2McTaa8
In this video learn how to take an invoice table, list the unique combinations of Invoice & Vendor and then list the items (references) horizontally. See the Old School Array Formula and the new Office 365 Dynamic Array Formulas. This formula takes a proper data set and does two things: 1) Lists the unique records, then 2) lists items horizontally from the reference column next to each extracted unique record.

View on YouTube

Custom Conditional Formatting For Power BI Tables

Posted on by Annie D

In Power BI we now have a wide variety of conditional formatting possibilites for tables. This wasn’t always the case.

During this tutorial I take you through a range of examples using conditional formatting rules in Power BI.

There is so much flexibility now to complete an almost unlimited amount of conditional formatting.

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

OFFSET Function Dynamic Range inside COUNTIFS Function – Excel Magic Trick 1573

Posted on by Annie D

Download Excel File: https://ift.tt/2GpZGqw
In this video learn how to count the number of 4.0 grades for each test. The complication is that as we copy the formula down a column, we need the range to move to the next column. Learn how to create a Dynamic Range using the COUNTIFS function.

View on YouTube