Category: Power Query

Excel Random No Repeats – Updated for Microsoft 365 – 2570

Posted on by Annie D

How to generate a random selection in Excel with no repeats.

Choose random names from the Data Set, but do it without repeating a namek. In a revisit to Episode #1471, how will Bill assure that there are no repeats in the Random selection? Follow along with Episode #2570 today to learn the answer and solve the challenge.

This video requires new Excel functions available in Microsoft 365. If you don’t yet have RANDARRAY, SORTBY, or SEQUENCE, then use the old method in video 1471: https://www.youtube.com/watch?v=g8FFgxmE70g

The PowerBall lotto formula shown near the end of the video is:
=TEXTJOIN(“, “,,SORT(INDEX(SORTBY(SEQUENCE(69),RANDARRAY(69)),
SEQUENCE(5))),”PB=”&RANDBETWEEN(1,26))

Table Continue reading “Excel Random No Repeats – Updated for Microsoft 365 – 2570” →

How do THIN reports work with Power BI Deployment Pipelines?

Posted on by Annie D

Using Power BI Deployment Pipelines and curious what happens when the report and dataset are in different workspaces? How does it work when you promote to another pipeline? Kevin Arnold shows us!

Kevin Arnold:
https://twitter.com/kevarnold
https://www.linkedin.com/in/kevarnold/

📢 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 “How do THIN reports work with Power BI Deployment Pipelines?” →

Art of the Possible with Power Platform and Azure Synapse

Posted on by Annie D

What happens when you combine Power BI, Power Apps and Azure Synapse Analytics? MAGIC! Join Gaston Cruz as he shows a way to bring the Power Platform together with Azure Synapse!

Gaston Cruz
https://twitter.com/GastonFCruz
https://www.linkedin.com/in/gastoncruz/

📢 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/
— rel=”nofollow”>https://guyinacube.com

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

#PowerBI #GuyInACube

Excel Shortcut To Unhide Columns Not Working – 2569

Posted on by Annie D

One of these four shortcut keys stops working in Windows versions of Excel:
Ctrl+9 to hide rows
Ctrl+Shift+9 to Unhide rows
Ctrl+0 to hide columns
Ctrl+Shift+0 to unhide columns
If the last one is not working for you, it is because the Windows shortcut to switch language is assigned to Ctrl+Shift. This video shows the Control Panel setting to make Ctrl+Shift+0 or Ctrl+Shift+) start unhiding columns again.

Why Is Excel Advanced Filter Using Partial Matching 2388

Posted on by Annie D

Today’s question from Bill.
Did you know that Excel’s Advanced Filter is returning partial matches?
A phrase in the criteria range is treated as Phrase*.
This is an annoying problem.

Excel Help sort of alludes to this problem and suggests a formula in the criteria range of Advanced Filter. But if you try to automate the creation of those formulas in the criteria range, it does not work.

In this video, a convoluted set of steps to automate formula creation with a carat in place of the equals sign, then Replace to change the text into a formula. This allows the Advanced Filter to work.

This Continue reading “Why Is Excel Advanced Filter Using Partial Matching 2388” →

How to SUM Only The Positive Numbers in Excel #shorts

Posted on by Annie D

How to sum only the positive numbers in Excel?
This video shows how to use SUMIF or SUMIFS.

This video matches these search terms:
how to calculate sum of only the positive numbers in excel
how to do a sum of only positive values in excel
how to sum negative and positive total numbers in excel
how to sum positive and negative in excel

Dataset permissions required for App report in Power BI? What am I missing??? (Re-Upload)

Posted on by Annie D

Struggling with Power BI App permissions when using shared datasets? Adam looks at how this works. Even for PRO users!!!

Composit model permission blog (Older information):
https://powerbi.microsoft.com/en-us/blog/update-to-required-permissions-when-using-composite-models-on-a-power-bi-dataset/

📢 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 – rel=”nofollow”>https://guyinacube.com/tools/

#PowerBI #Permissions #GuyInACube

Does XLOOKUP Approximate Match Work With Text – 2387

Posted on by Annie D

A question from my Daytona Beach seminar last week:
We know XLOOKUP can find the number just larger or just smaller than a number. But does this also work with Text? How about with Symbols?

In this video, Bill Jelen shows how the approximate match works with XLOOKUP. Unlike MATCH or VLOOKUP, the table does not have to be sorted. You can find a value just smaller or just larger than the search value.

This also works with Text, although it is case insensitive.

And, it works with symbols, but in an unexpected sequence.

The POWER of Microsoft Lists and Power BI

Posted on by Annie D

Learn how Microsoft Lists / SharePoint Lists can take advantage of Power BI! Daniel walks through how easy it is to start using today!

Create a report quickly from a SharePoint list or library
https://learn.microsoft.com/power-bi/create-reports/service-quick-create-sharepoint-list

Daniel Glenn:
https://twitter.com/DanielGlenn
https://www.linkedin.com/in/danielglenn/
https://365mcs.com/
https://www.youtube.com/365mcs

📢 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
— Continue reading “The POWER of Microsoft Lists and Power BI” →

Excel How to Sum Non Contiguous Cells #shorts

Posted on by Annie D

How to use the AutoSum when you need to sum non-contiguous cells in Excel.

This video was created to answer these search terms:
How To Sum Non-Contiguous Cells In Excel?
How To Sum Specific Cells In Excel Formula
How To Count Sum For A Particular Cell In Excell
How To Count Sum Of Certain Cells In Excel
How To Select Cells In Excel For Sum
How To Sum Certain Cells In Excel
How To Sum Different Cells On Excel
How To Sum Different Columns In Excel
How To Sum Different Rows In Excel
How To Sum Select Cells In Excel
How To Sum Two Cells In Excel

The Excel Status Bar Uses Number Format From Active Cell – 2386

Posted on by Annie D

Did I know this? The Excel Status Bar Statistics pick up the number format of the active cell in the grid. This is a useful feature, and not a bug. But it could freak someone out when you get different answers depending on if you select from bottom to top or top to bottom.

Also in this episode: the four zones of Custom Number Formats.

And, the winner(s) of a free Excel PDF.