Month: October 2022

Excel 75 Minute Process To 13 Seconds – 2523

Posted on by Annie D

In this video, several of my friends and I propose ways to cut a 75-minute Excel process down to under 15 seconds. As expected Diarmuid Early posts the fastest time with an under-10-second solution. But Diarmuid warns about a possible problem in the data, which makes a method proposed by Peter_SSs and Anup Agarwall the fastest and safest method.

The formula methods from others:
https://www.mrexcel.com/board/threads/excel-insert-blank-row-after-every-two-data-rows-shorts.1217116/
https://www.mrexcel.com/board/threads/insertblankat.1217374/

Table of Contents
(0:00) Hurricane Ian delay
(0:34) 2-minute solution for OP
(1:11) 53-Second solution
(1:30) Too Slow
(2:10) Fastest Excellers
(2:30) Anup Agarwal
(3:07) Diarmuid Early I
(4:06) Diarmuid Early II
(4:35) Imagining OP data
(4:52) Other solutions
(5:09) Formula dependencies between Continue reading “Excel 75 Minute Process To 13 Seconds – 2523” →

Excel Sort A Column By Font Width ? 2522

Posted on by Annie D

After sorting by number of characters in a cell, we get a variant from Thinks Too Much (b): Can you sort, factoring in the width of the letters in the font that is being used?

Table of Contents
(0:00) Welcome Back
(0:12) Question
(0:40) VBA code to measure width
(1:44) Differing fonts & sizes
(2:15) Nancy Faust

Here is my VBA solution:
Sub SortAscCustomerWidth()
FinalRow = Range(“E1048576”).End(xlUp).Row
OrigWidth = Columns(5).ColumnWidth
HelperColumn = Range(“XFD1”).End(xlToLeft).Column + 1
Cells(1, HelperColumn).Value = “Helper”

‘ Loop
For Each cell Continue reading “Excel Sort A Column By Font Width ? 2522” →

Excel Sort A Column By Number Of Characters – 2521

Posted on by Annie D

How do you sort a column in Microsoft Excel by the number of characters? This video was inspired by a question originally posted at Quora: https://www.quora.com/How-do-you-sort-a-column-in-Microsoft-Excel-by-the-number-of-characters/answer/Bill-Jelen

Table of Contents
(0:00) Welcome
(0:13) SORTBY
(0:49) Problems!
(1:06) VSTACK
(1:30) Copy & Paste Values
(1:47) Easy Helper Column

How To Filter By A Specific First Letter Or Character In Power Query

Posted on by Annie D

In this video, Melissa demonstrates a couple of approaches in solving Excel BI’s Table Filter Challenge. The goal is to filter out names where the 2nd word starts with the letter “C”.

Excel BI’s Table Filter Challenge: https://bit.ly/3TohSEm

***** Video Details *****
00:00 Introduction
01:06 1st approach
03:01 2nd approach
03:44 Split words into a list

***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI – http://portal.enterprisedna.co/p/ultimate-beginners-guide-to-power-bi
FREE COURSE – Ultimate Beginners Guide To DAX – http://portal.enterprisedna.co/p/ultimate-beginners-guide-to-dax
FREE – Power BI Resources – http://www.enterprisedna.co/power-bi-resources
FREE – 60 Page Continue reading “How To Filter By A Specific First Letter Or Character In Power Query” →

A better way to share Personal Bookmarks in Power BI

Posted on by Annie D

A while back Adam shared how you can share a personal bookmark with others. There is now a better way to share personal bookmarks in Power BI if your report is in a workspace. Learn how!

Share Power BI reports and dashboards with coworkers and others
https://learn.microsoft.com/power-bi/collaborate-share/service-share-dashboards

Alternative to sharing a Power BI Personal Bookmark
https://www.youtube.com/watch?v=-c1-VQ6RsrA

📢 Become a member: https://guyinacu.be/membership

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

Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.

🎓 Guy in a Cube courses: https://guyinacu.be/courses

*******************
LET’S CONNECT!
*******************

— rel=”nofollow”>http://twitter.com/guyinacube
— http://twitter.com/awsaxton
— http://twitter.com/patrickdba
— http://www.facebook.com/guyinacube
— https://www.instagram.com/guyinacube/
— https://guyinacube.com

***Gear***
🛠 Check out my Tools page – https://guyinacube.com/tools/

#PowerBI #Bookmarks #GuyInACube

Excel Easy Collapsible Outline View – 2521

Posted on by Annie D

Here is a great trick I learned a decade ago from Mack Wilk.
I have a long list of topics in Excel.
I want to convert it to a collapsible outline view.

There is one secret hidden setting that you need to change first.
Then, it is all very easy.

Join the MrExcel community: https://www.mrexcel.com/community/

Table of Contents
(0:00) Need for outline in Excel
(0:28) Secret hidden setting
(0:48) Select detail rows for group 1
(1:05) Group & Outline buttons
(1:25) Select rows with Shift Spacebar
(1:45) Using the Group buttons
(2:09) Third Thursday Webinar

This video answers these common questions:
what does grouping cells in excel do?
what does grouping Continue reading “Excel Easy Collapsible Outline View – 2521” →

How To Compare Performance Using The ALL Function In Power BI [2022 Update]

Posted on by Annie D

There will come a time that you would want and need to compare performance by looking at a particular selection in your dataset versus the overall result. This is where the ALL function in DAX comes in. In this tutorial, Sam shows you how easily and effectively find differentials in your data by using the ALL function.

***** Video Details *****
00:00 Introduction
00:53 Getting the average order size
03:01 Comparing the average order size
04:12 Differentiating the average order size

***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI – http://portal.enterprisedna.co/p/ultimate-beginners-guide-to-power-bi
FREE COURSE – Ultimate Beginners Guide Continue reading “How To Compare Performance Using The ALL Function In Power BI [2022 Update]” →

All Important Excel Lookup Formulas: Excel Worksheet, Power Query & DAX – 28 Examples! – 365 MECS 08

Posted on by Annie D

Download Excel file: https://excelisfun.net/files/08-M365ExcelClass.xlsx
Download pdf notes: https://excelisfun.net/files/08-M365ExcelClass.pdf
Course taught by Excel MVP and Highline College Professor, Mike Girvin. Course is Microsoft 365 Excel Complete Story.
Topics in video:
1. (00:00) Introduction
2. (01:35) XLOOKUP function
3. (02:04) Exact Match Lookup
4. (04:00) Data Validation List
5. (07:19) Lookup a Record
6. (08:50) Lookup a Column
7. (10:37) Exact Match or Next Smaller Lookup (Approximate Match Lookup)
8. (14:24) Vertical or Horizontal Lookup?
9. (15:40) Lower Limit for Exact Match or Next Smaller Lookup
10. (16:48) Exact Match or Next Bigger Lookup
11. (21:15) Wild Card Lookup
12. (23:10) Lookup First Item
13. (24:57) Lookup Last Item
14. (26:11) Lookup Continue reading “All Important Excel Lookup Formulas: Excel Worksheet, Power Query & DAX – 28 Examples! – 365 MECS 08” →

Why isn’t my Agg being hit in Power BI???

Posted on by Annie D

There are different reasons why your Agg isn’t being hit in Power BI. In this video, Patrick looks at a specific reason based on the DAX functions you are using. Gotta watch out!

📢 Become a member: https://guyinacu.be/membership

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

Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.

🎓 Guy in a Cube courses: https://guyinacu.be/courses

*******************
LET’S CONNECT!
*******************

— http://twitter.com/guyinacube
— http://twitter.com/awsaxton
— http://twitter.com/patrickdba
— http://www.facebook.com/guyinacube
— https://www.instagram.com/guyinacube/
— https://guyinacube.com

***Gear***
🛠 Check out my Tools Continue reading “Why isn’t my Agg being hit in Power BI???” →

How To Use ECDF Plots With Python And Power BI

Posted on by Annie D

In this video, Gaelim demonstrates how to use a Cumulative Distribution Plot also know as Empirical Cumulative Distribution Function (ECDF) Plot, and showcase the advantages of using this variation of plot over other plot types.

***** Video Details *****
00:00 Introduction
00:34 Kinds of distribution plots
04:34 Histogram plot code
06:07 KDE plot code
06:23 Box plot code
06:50 ECDF plot code
08:05 Styling plots

***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI – http://portal.enterprisedna.co/p/ultimate-beginners-guide-to-power-bi
FREE COURSE – Ultimate Beginners Guide To DAX – http://portal.enterprisedna.co/p/ultimate-beginners-guide-to-dax
FREE – Power BI Resources – http://www.enterprisedna.co/power-bi-resources
FREE – Continue reading “How To Use ECDF Plots With Python And Power BI” →

Parameterize your Notebooks in Azure Synapse

Posted on by Annie D

Using notebooks in Azure Synapse Analytics? Do you find yourself wanting to have a dynamic value for use with your pipelines? Patrick shows you how you can accomplish this!

📢 Become a member: https://guyinacu.be/membership

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

Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.

🎓 Guy in a Cube courses: https://guyinacu.be/courses

*******************
LET’S CONNECT!
*******************

— http://twitter.com/guyinacube
— http://twitter.com/awsaxton
— http://twitter.com/patrickdba
— http://www.facebook.com/guyinacube
— https://www.instagram.com/guyinacube/
— https://guyinacube.com

***Gear***
🛠 Check out my Tools page – <a href=”https://guyinacube.com/tools/” Continue reading “Parameterize your Notebooks in Azure Synapse” →

Best Practice Tips For Using The Advanced Editor In Power BI [2022 Update]

Posted on by Annie D

In this tutorial, Sam runs through some of his best practice tips for using the query editor in Power BI. The query editor is undoubtedly important in Power BI, but it is so hidden away that those who are just starting out might not easily know that it exists. Power BI users will ultimately need to learn how to use it, and use it well. Check out Sam’s tips to help you navigate the query editor efficiently.

***** Video Details *****
00:00 Introduction
00:50 Using the query editor
04:14 Making changes in the editor
05:08 Fixing the error

***** Learning Power BI? *****
FREE COURSE – Continue reading “Best Practice Tips For Using The Advanced Editor In Power BI [2022 Update]” →