Category: Power Query

Excel Holidays Between Two Dates – 2369

Posted on by Annie D

After episode 2363, a pair of great questions and a tip.
Tip 1: When specifying the Holiday Range in Excel, make the range into a table and then name the range. That way, when you add new holidays at the bottom, the named range will automatically expand.
Question 1: How many holidays fall between two dates?
Question 2: What is the list of holidays between two dates?

This video includes details on NETWORKDAYS.INTL and FILTER functions.

You will also see how I take two sub-formulas that were in different cells and combine them into a single formula by scooping the characters out of each formula.

Table Continue reading “Excel Holidays Between Two Dates – 2369” →

Continuing the T-SQL journey with OVER and RANK

Posted on by Annie D

Continuing our T-SQL journey, Patrick explores the use of the OVER and RANK functions in combination with common table expressions (CTE) in Azure SQL Database and Azure Synapse Analytics.

RANK (Transact-SQL)
https://learn.microsoft.com/sql/t-sql/functions/rank-transact-sql?view=sql-server-ver16

SELECT – OVER Clause (Transact-SQL)
https://learn.microsoft.com/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-ver16

WITH common_table_expression (Transact-SQL)
https://learn.microsoft.com/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver16

📢 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
— Continue reading “Continuing the T-SQL journey with OVER and RANK” →

Excel Hold Alt+1 To Repeatedly Run 1st QAT Icon Perhaps Increase Decimal 2368

Posted on by Annie D

A cool Excel trick from Geert Delmulle – you can increase decimal repeatedly by holding down Alt+1. This works if you move the Increase Decimal icon to one of the first 9 spots on the Quick Access Toolbar.

Table of Contents
(0:00) Fast Way to Increase Decimal in Excel
(0:47) After Alt key, use 1-9 for first 9 QAT icons
(1:02) Hold Alt+2 to run 2nd QAT icon repeatedly
(2:30) Adding icons to the Excel QAT
(2:54) Moving QAT icon to beginning
(3:06) Finding icons not on Ribbon for QAT
(4:06) Save icon too far left after AutoSave removed

Hide Excel Pivot Table Rows Using Sets And Then Edit The MDX – 2367

Posted on by Annie D

If you need to hide some rows in an Excel pivot table, you can Create a Set Based on Row Items.

This is a rarely used feature: First, it is only available if you choose Add This Data To The Data Model.

Once you create a set, it is a bit strange to remove rows. And if you add data later, you have to go back in to edit the set. Excel offers to let you edit the MDX, but what is MDX?

Thanks to David Jefferson who provided a comment to a YouTube video, he shows how to Continue reading “Hide Excel Pivot Table Rows Using Sets And Then Edit The MDX – 2367” →

Excel Suggest The Next Awesome Excel Idea – 2366

Posted on by Annie D

Do you love Excel? Do you have a feature that would make Excel easier for you? Write up your idea and post it to the new Feedback Forum for the Excel team. Other people can vote for your idea and many ideas each year are added to Excel.

The Feedback portal for Excel is at: https://feedbackportal.microsoft.com/feedback/forum/c23f3b77-f01b-ec11-b6e7-0022481f8472

Vote for my idea to use the Custom Lists when renaming copied worksheets, vote here: https://feedbackportal.microsoft.com/feedback/idea/13963988-a0a0-ed11-a81b-000d3a0450e3

If you post a great idea at the Feedback forum, let me know the title in a YouTube comment and I will vote Continue reading “Excel Suggest The Next Awesome Excel Idea – 2366” →

🔴 Power BI tips from the Pros – LIVE (Feb 4, 2023) (Member Chat 2nd Half)

Posted on by Annie D

Join us live as we answer your questions about Power BI!

💥 30 minutes: Open Q&A (Public)
💥 Then… Members Only Chat (Public can watch & Super Chat for Questions)

📢 RULES FOR Q&A: 📢

👉 Put a “Q:” in front of your comment to help us identify questions!
👉 Super chats take priority
👉 Do NOT re-post your questions! We’ll get to as many questions as we can.
👉 If you SPAM your question, you will be put in timeout

📢 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 Continue reading “🔴 Power BI tips from the Pros – LIVE (Feb 4, 2023) (Member Chat 2nd Half)” →

Let’s see if ChatGPT knows what’s coming next for Power BI…

Posted on by Annie D

Everyone is making videos on ChatGPT and trying to solve problems. What about telling the future? Let’s see if ChatGPT can tell us what’s coming next for Power BI!

📢 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 “Let’s see if ChatGPT knows what’s coming next for Power BI…” →

Excel Reverse Wildcard Search – 2365

Posted on by Annie D

VLOOKUP, XLOOKUP, and SUMIFS all allow a wildcard in the Lookup_Value. But today, Chris wants wildcards wrapped around the lookup table! After trying XLOOKUP, FILTER, and COUNTIF, I finally end up with TEXTJOIN, IF, and SEARCH. Do you have a better way?

Table of Contents
(0:00) Use wildcard for the lookup range
(0:34) XLOOKUP, FILTER, COUNTIF failed
(0:54) SEARCH function on an array
(2:11) TEXTJOIN, IFERROR, and SEARCH
(3:15) Surprisingly difficult

Calling a Stored Procedure using Incremental Refresh in Power BI? We think so!

Posted on by Annie D

Have you wanted to use a Stored Procedure with Incremental Refresh? Patrick has a way that you can do just that!

Sandeep’s blog:
https://pawarbi.github.io/blog/power%20bi/powerquery/queryfolding/m/optimization/2022/01/25/parameter-valuenativequery-query-folding-where-clause-in-powerbi.html

Stored Procedures: https://learn.microsoft.com/sql/relational-databases/stored-procedures/stored-procedures-database-engine?view=sql-server-ver16

Sample:
https://github.com/guyinacube/demo-files/blob/master/video%20demos/PBIX/2023/20230201%20-%20IR%20with%20SP.zip

📢 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 Continue reading “Calling a Stored Procedure using Incremental Refresh in Power BI? We think so!” →

Excel Secret To Maximizing Euro Jackpot Winnings – 2364

Posted on by Annie D

Markus is looking for Excel help to pick numbers for the Euro Jackpot where you have to choose 5 numbers from 1 to 50. In this video, I share secrets from my late friend Dogtrack Bill.

To use the number picker in a free Excel Online, follow this link:
https://1drv.ms/x/s!As7G72Sl487JljvjcWPxwg0_pTe1?e=CMGO1E

The Euro Jackpot payout for matching 5 numbers with no bonus numbers is a parimutuel system. A fixed percentage of the betting pool is split between all people that matched five numbers. To maximize the payout if you win, choose numbers that are not likely to be chosen by Continue reading “Excel Secret To Maximizing Euro Jackpot Winnings – 2364” →