The new XLOOKUP function debuts for Excel Insiders Fast today (August 28 2019) at Noon eastern time. Here is a first look at all of the cool things you can do with XLOOKUP.
Tag: How to
Troubleshoot missing applied filters in Power BI
Have you ever seen that a visual is being filtered and you can’t find where that filter is? Whether it has happened to you yet or not, make sure you check this video so you know how to fix it when if happens. #curbal #powerbi
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
Array Formula: Filter Rows with List of Does NOT Contain Criteria – Excel Magic Trick 1595
Download Excel File: https://ift.tt/2ZvPfrY
Learn how to filter the rows of a table with a List of Does NOT Contain Criteria using an Array Formula. See the Excel Worksheet Functions: SEARCH, ISNUMBER, MMULT, TRANSPOSE, ROW, FILTER Dynamic Array Formula, Also, catch a gimps of the functions: INDEX, AGGREGATE, IF, ROWS, SUMPRODUCT and more.
This complex Array formula is amazing because it uses traditional Array Array Functions like MMULT and TRANSPOSE and the new Dynamic Array Functions like FILTER, all with the new Excel Calculation Engine!!!
Hide Zero Values in Excel | Make Cells Blank If the Value is 0
In this video, I will show you how to hide zero values in Excel using multiple methods.
The video also covers a method to remove zero values in Excel.
There are multiple ways you can hide zero values in Excel:
— Using an in-built functionality that hides all the zeros in the cells in the selected worksheets
— Using conditional formatting to change the font color of the cells that have 0 in it
— Using conditional formatting to apply a custom format that hides zero values
— Using a custom format that specifies blank as the format for cells that have 0 in it
And in case you don’t want to hide the zeroes but instead remove these, you can use Find and Replace. With Find and Replace, you can instantly find and select all the cells that have 0 in it. Once you have these selected, you can either remove these or format it or enter any specific text in it.
NOTE: When you hide a zero value in Excel, it only hides the value from being visible. It still remains a part of the cell and in case you use this cell in any calculation, the zero value will be used.
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
Transform vs Add column in power query
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
Advanced Filter: Filter Rows with List of Does NOT Contain Criteria – Excel Magic Trick 1594
Download Excel File: https://ift.tt/2HoS1ZU
Learn how to filter the rows of a table with a List of Does NOT Contain Criteria using Advanced Filter. See two methods: Traditional Criteria Area for the Advanced Filter and with a Criteria Area Formula.
Power Query Filter Rows by NOT Contains Criteria – Single Formula Solution
Learn how to filter a table based on NOT Contains Criteria. Download an example workbook here.
Easy step by step instructions below. See a single formula solution using the functions Splitter.SplitByAnyDelimiter, List.Count and Table.SelectRows. Amazing formula solution from Power Query Poet, Bill Szysz.
Please watch the video if you’d like for a guided walkthrough and also another method that can be useful for multiple tables.
Criteria Table
- Create a new, separate table with the list of terms you will want to exclude. Name the table “NoCriteria”.
- Add the excluded item (NoCriteria) table to Power Query – click within table, under Data menu, choose From Table/Range, which is in the Get and Transform data section.
- In the Power Query window, select the Transform menu and click convert to list.
- Under the File menu, choose Close and Load To, then choose Connection Only.
Building the Filtering via Power Query
- Add or create the list/table that will ultimately be filtered.
- Click any cell within the table that will be filtered. Add the excluded item table to Power Query (click within table, under Data menu, choose From Table/Range, which is in the Get and Transform data section).
- In the Power Query window, click the Add Column menu, and select Custom Column.
- In the window that opens, type this:
= Table.SelectRows(#”Changed Type”, each List.Count (Splitter.SplitTextByAnyDelimiter(NoCriteria)([PRODUCT_NAME]))=1)
- Change [PRODUCT_NAME] in that text to your own column in the table that you will be filtering on if it is different.
- Select the statement you have typed in and copy it (you will need to paste this formula in a following step).
- Click OK. You will see that the formula you typed was changed by the program and a column was added.
- To change the formula back, click the menu bar, highlight the entire text and then replace by pasting in the formula you copied. Hit enter.
- The extra column should be removed and the table should be filtered on you criteria from the NoCriteria table.
- Click the File menu, choose Close and Load To, then choose where you would like the newly filtered table loaded to.
Any time changes are made to the exclusion list, you will need to refresh the filtered table. Simply right click any cell within the filtered table, and select Refresh.
Bonus – Filtered table with the excluded items only (not shown in video)
You can additionally create a filtered table that only includes the terms in your NoCriteria table!
- To do this, go into Power Query. Right click on your filtered table and click Duplicate.
- In that new table, you will very slightly change the existing formula in your Power Query to not equal one (see orange text):
= Table.SelectRows(#”Changed Type”, each List.Count (Splitter.SplitTextByAnyDelimiter(NoCriteria)([PRODUCT_NAME]))<>1)
- Hit enter. The table should now only filter on the items in your exclusion list, instead of including them.
- Click the File menu, choose Close and Load To, then choose where you would like the newly filtered table loaded to.
One PBI insaller, filter pane and more… Power BI desktop update July 2019, August 2019
One installer for all power bi versions, filter pane is now generally available and more updates on this power bi desktop update july 2019!!!
#curbal #powerweek #powerbi
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
Power Query – Filter Table Based on Distinct NOT Conditions from List – Excel Magic Trick 1592
Download Excel File: https://ift.tt/2KJrSHe
Learn how to filter imported data using Distinct NOT Conditions from List. See a single M Code Power Query Formula using the functions. Table.SelectRows, Text.Split, List.Intersect and List.IsEmpty. This amazing solution comes from Bill Szysz.
Deep dive on icon sets – Power BI desktop update July and August 2019
In today’s video, I will review the icon set feature available in Power BI since July 2019. I will give you my thoughts on what is good and what can be improved.
What do you think?
Here are the links mentioned in the video:
KPI with a measure: https://www.youtube.com/watch?v=cboA5AtAPeg
08:22 How to download Amandas files: https://www.youtube.com/watch?v=zpT8s2S8E9w
08:50 Deep dive on SVG: https://www.youtube.com/watch?v=YU44czu8SD8
Embed png in power bi: https://www.youtube.com/watch?v=fhtCSUk1pUA
11:20 Link to icon names: https://ift.tt/31RIYbS
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
One Lookup Value, Return Multiple Items – The Easy Way with FILTER – Excel Magic Trick 1591
Download Excel File: https://ift.tt/30eo31Z
Learn how to return multiple items with one lookup value using the new Office 365 FILTER Dynamic Array Function. What to do if you have one lookup value and duplicate matches and you want to return multiple items?
Split column by position – Power BI desktop update July 2019
Splitting columns by position is not as straight forward as you might think. In this video I show you the proper way to do it to avoid mistakes.
#curbal #powerbi #powerquery #powerweek
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
#MAGICMONDAYS #CURBAL #POWERQUERY #POWERBI #MVP