Download Excel File: https://ift.tt/2zCE5af
Learn how to create a formula to extract items in List 2 that are not in List 1. Also see second formula to extract items in both lists. Learn about the MATCH & ISNA functions for “NOT in List”. Learn about MATCH & ISNUMBER for “Is In List”. See the Office 365 FILTER Function that mekas extract values from the list easy. Also see a references to the Old School Array Formula Method for extracting items from Two Lists.
Month: August 2019
Calculate Days Of Zero Stock – Inventory Management Insights w/Power BI
Recently we had an interesting request on the Enterprise DNA Support Forum where a member needed to workout the number of days their inventory stock levels were at 0.
The technique required to work this out was unique because of the data set up that the member had.
Some different DAX formula techniques were used to solve this.
Enjoy working through this one!
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
XLOOKUP or INDEX-MATCH-MATCH Head-to-Head – 2287
The new XLOOKUP for Excel debuted yesterday. Can it replace INDEX-MATCH? Can it replace INDEX-MATCH-MATCH? The answer is Yes!
Excel Formula to Compare Two Lists – Excel Magic Trick 1596. Is Item in List?
Download Excel File: https://ift.tt/2Hsppio
Learn how to create a formula to compare two lists. See Two Formulas. One formula to ask question “Is Item in List?”. One formula to ask the question “Is Item NOT in List?”. Learn how to use the MATCH, ISNUMBER and ISNA functions. See a bonus formula that uses the VLOOKUP Function.
XLOOKUP in Excel is VLOOKUP Slayer! 2286
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.
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