Category: Business

3 Power Query Formulas: Hours Worked for Day & Night Shift from Time Values. Excel Magic Trick 1827

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1826-1827.xlsx
Learn about how to calculate hours worked from time values in Power Query. Learn about the importance of M Code Values and Data Types when creating formulas. See three different formulas.
Topics:
1. (00:00) Introduction.
2. (00:21) How Power Query Formulas are different than in the Worksheet and DAX formulas.
3. (01:55) Hours Worked From Time Values for Day Shift using Duration.TotalHours function.
4. (03:13) Hours Worked From Time Values for Night Shift using let expression, the Number.From Function and Number.RoundDown function.
5. (06:45) Hours Worked From Time Values for Night Shift using the Number.From Function and a Logical Test.
6. Continue reading “3 Power Query Formulas: Hours Worked for Day & Night Shift from Time Values. Excel Magic Trick 1827” →

The MAGIC of Power BI in PowerPoint!

Posted on by Annie D

There were some recent updates to the storytelling experience in PowerPoint with Power BI. Patrick walks through how you can use these to make the most of PowerPoint and Power BI!

About storytelling with Power BI in PowerPoint
https://learn.microsoft.com/power-bi/collaborate-share/service-power-bi-powerpoint-add-in-about

šŸ“¢ 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/
— <a Continue reading “The MAGIC of Power BI in PowerPoint!” →

Leveraging OneLake Explorer – The OneDrive of Data! (Microsoft Fabric)

Posted on by Annie D

It’s been said that OneLake is the OneDrive of data in Microsoft Fabric! OneLake Explorer makes that even more so. Patrick walks you through how this works and how you can leverage it today!

OneLake File Explorer
https://learn.microsoft.com/fabric/onelake/onelake-file-explorer

šŸ“¢ 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/
— <a href="https://guyinacube.com" Continue reading “Leveraging OneLake Explorer – The OneDrive of Data! (Microsoft Fabric)” →

Formula to Calculate Hours Worked in Excel & DAX: Day or Night Shift! Excel Magic Trick 1826

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1826-1827.xlsx
Learn how to take time values and calculate hours worked for day or night shift with Excel formulas or DAX Formulas. Lean about the famous MOD function to make this calculation easy. Learn how the MOD function algorithm works.
Topics:
1. (00:00) Introduction
2. (00:28) Time as proportion of 24 hours.
3. (01:20) Calculate hours work when there is no nightshift.
4. (01:48) Formula #1: Logical Formula. Calculate hours work when there are some who work past midnight, or the nightshift.
5. (03:32) Formula #2: MOD Function. Calculate hours work when there are some who work past midnight, or the Continue reading “Formula to Calculate Hours Worked in Excel & DAX: Day or Night Shift! Excel Magic Trick 1826” →

Embedding Excel Range in a Word Document – Strategic Finance Magazine July 2023

Posted on by Annie D

How to create a live link from an Excel workbook to a Word document.
This video is to accompany the article in the July 2023 issue of Strategic Finance Magazine: https://www.sfmagazine.com/articles/2023/july/excel-embedding-a-range-in-a-word-document

Excel Improvements To Picture In Cell – 2608

Posted on by Annie D

Microsoft Excel Tutorial: Adding Filterable Text to a Picture in Cell

The new Picture in Cell feature would not work with the Filter drop-downs. A new feature allows you to add text to each picture using the Alt Text panel. This Alt Text will appear in the Filter drop-downs, in XLOOKUP results, in the Pivot Table Filter, and in Pivot Table Slicers.

To download this workbook: https://www.mrexcel.com/youtube/xZxKhm9_Xkk/

Table of Contents
(0:00) Problem Statement: Filtering by Picture
(0:53) Use Alt Text to add Text to Picture in cell
(1:17) Alt Text appears in Filter drop-down
(1:44) Getting Picture from XLOOKUP
(2:15) Use Image in Continue reading “Excel Improvements To Picture In Cell – 2608” →

Power Query Dates Are Better Than Excel Worksheet Dates! Excel Magic Trick 1825

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1825.xlsx
Learn about how Power Query Dates are different from Excel Worksheet Dates. Learn that dates in Power Query range from 1/1/0001 to 12/31/9999. Learn how to add and subtract dates in Power Query, Excel too.
Topics:
1. (00:00) Introduction
2. (00:16) Excel & Lotus 1-2-3 Dates have two problems. Power Query solved these problems.
3. (00:53) Date Serial Numbers are Different in Power Query.
4. (01:47) Date Data Type
5. (02:08) Extract Year from Date
6. (02:20) Subtract Two Dates using Duration.Days function.
7. (02:57) Add Days to a Date using Date.AddDays function
8. (03:36) Adding and Subtracting Dates in the Excel Worksheet
9. Continue reading “Power Query Dates Are Better Than Excel Worksheet Dates! Excel Magic Trick 1825” →

Excel Formula to Take a Random Sample: 4 Methods. Excel Magic Trick 1824

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1824.xlsx
Learn how to take a random sample from a population based on a uniform distribution. See 4 Methods. 2 that work in any version of Excel and 2 that work in M 365 Excel.
Topics:
1. (00:00) Introduction
2. (00:14) RAND and RANDARRAY functions create random numbers based on a uniform distribution.
3. (00:31) M 365 Excel Formula #1: TAKE, SORTBY, RANDARRAY Functions.
4. (02:26) Formula for any version of Excel #1: SMALL, RAND, MATCH, INDEX, ROWS, IF and ROWS functions.
5. (05:30) M 365 Excel Formula #1: XMATCH, SMALL, RAND, INDEX, IF and ROWS Functions. Also see an array Continue reading “Excel Formula to Take a Random Sample: 4 Methods. Excel Magic Trick 1824” →

Excel Formula To Randomly Lookup Pictures or Any Item. Excel Magic Trick 1823

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1823.xlsx
Learn about how to create a formula to randomly lookup items without repeat. Lookup pictures or any item. See the functions LET, ROWS, RANDARRAY, SORT, INDEX, XMATCH.
Topics:
1. (00:00) Introduction
2. (00:12) Formula to do random lookup with no repeats. See the functions LET, ROWS, RANDARRAY, SORT, INDEX, XMATCH.
3. (02:36) Closing, Video Links

#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #powerbi #powerquery #powerbidesktop #freeclass #freecourse #freeclasses #excelclasses #powerquery #powerquerytutorial #microsoftexcel #microsoftmvp #sortfunction #indexfunction #matchfunction #XMATCH

Create MEANINGFUL data labels with measures in Power BI

Posted on by Annie D

Tired of the usual text data labels that only have one value? Looking for something different? Patrick shows you how to customize your data labels using DAX in your Power BI reports!

šŸ“¢ 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 “Create MEANINGFUL data labels with measures in Power BI” →

Lookup Picture with Excel Formula ā€“ XLOOKUP or FILTER – Excel Magic Trick 1822

Posted on by Annie D

Download Excel File and zipped folder of pictures: https://excelisfun.net/files/EMT1822.zip
Learn about the new M 365 Excel Lookup Picture ā€œPlace In Cellā€ feature with allows you to easily insert pictures and then use the lookup functions XLOOKUP and FILTER to lookup pictures and to use pictures as lookup values.
Topics:
1. (00:00) Introduction
2. (00:22) Lookup picture with XLOOKUP
3. (01:05) Picture as lookup value in XLOOKUP
4. (01:29) Lookup picture with FILTER
5. (02:29) Closing, Video Links

#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #powerbi #powerquery #powerbidesktop #freeclass #freecourse #freeclasses #excelclasses #powerquery #powerquerytutorial #microsoftexcel #microsoftmvp #xlookup #filter #filterfunction #lookup #picture