Category: Power Query

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” →

Ready to be a Kusto Detective with Azure Data Explorer?

Posted on by Annie D

Your detective journey starts with the Kusto Detective Agency for Azure Data Explorer. This is a really fun way to learn the Kusto Query Languate (KQL)! Patrick gets you started! And, it’s FREE!

Kusto Detective Agency:
https://detective.kusto.io/

📢 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/

#ADX #Kusto #GuyInACube

Excel Use Previous Workday If Not A Workday – 2363

Posted on by Annie D

Vicki: I have a list of dates and I’m looking for a formula to calculate the previous workday only if the date in the list is a weekend or holiday.

Why: Accrue Fed Fund Interest Daily (Fed Funds Rate only published on Business Days).

This is mildly complex, because you need to be able to create a named range for the holidays, and after building the long formula, Excel will likely give you the date serial number instead of the formatted date.

Table of Contents
(0:00) Introduction
(0:23) Create a named range with holidays
(1:23) Excel formula to detect if date is a workday using NETWORKDAY Continue reading “Excel Use Previous Workday If Not A Workday – 2363” →

🔴 Power BI tips from the Pros – LIVE (Jan 28, 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 (Jan 28, 2023) (Member Chat 2nd Half)” →

Free Excel Templates From Microsoft Create – 2361

Posted on by Annie D

The Microsoft Create platform is in preview. Sign in with a Microsoft 365 Home account to access free templates for Excel and more. It also includes training on social media from Kat Norton Miss Excel and promises future features.

Table of Contents
(0:00) Free templates in Preview
(0:34) Start at Create.Microsoft.Com
(1:30) Opening a template in Excel Online
(1:54) Accessing from Win32
(2:24) Learning videos including Kat Norton
(3:00) Designer & Clipchamp coming

What do you want to see from Guy in a Cube? – Channel Update

Posted on by Annie D

It’s 2023 and there are amazing things ahead! Adam looks at what will be coming for the Guy in a Cube channel, and more importantly has a question for YOU!

📢 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 Continue reading “What do you want to see from Guy in a Cube? – Channel Update” →

Less F9 In The Excel Formula Bar And Two Other New Features – 2360

Posted on by Annie D

Three new features coming to Excel.
1) Value Preview will evaluate part of your formula when you select it in the Excel formula bar. You don’t have to press F9 and Esc anymore.
2) AutoSave moves from an icon to Words in the Excel Title Bar
3) Small preview of Personalized Toolbar in Excel

Table of Contents
(0:00) Three new Excel Features
(0:30) F9 replaced with Value Preview
(3:57) AutoSave icon moves to Title Bar words
(5:07) Personalized Toolbar in Preview
(10:38) Wrap-up

What happens when you miss a refresh day with Power BI Incremental Refresh?

Posted on by Annie D

You’ve set up Power BI Incremental Refresh and there is a day that is missed because schedule refresh failed or some other reason. What happens? Do you lose that data? Patrick digs in!

📢 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 “What happens when you miss a refresh day with Power BI Incremental Refresh?” →

What is a Slowly Changing Dimension???

Posted on by Annie D

Want to know more about a slowly changing dimension? What about the different types? Patrick goes through examples and ties in the surrogate key!

Sample: https://github.com/guyinacube/demo-files/blob/master/video%20demos/Synapse/20230124%20-%20SCD%20Demo.sql

Handling Type II Dimension with the MERGE Statement
https://sqldownsouth.blogspot.com/2010/11/handling-type-ii-dimension-with-merge.html

Slowly Changing Dimension
https://en.wikipedia.org/wiki/Slowly_changing_dimension

#PowerBI #GuyInACube

Excel Power Query Import And Clean Fixed Width Text Files 2359

Posted on by Annie D

I was doing a seminar recently and many people at the company were getting reports downloaded as fixed width text files.

I encouraged them to start using the Get & Transform tools in Excel for cleaning this date. After the seminar, I created a text file with many of the issues that were in their workbooks.

Table of Contents
(0:00) Fixed Width Text File for Excel
(0:50) Excel Text Import Wizard UI for Marking Column Locations
(1:19) Finding column start locations for Power Query
(1:59) Importing to Excel from Text/CSV
(2:18) Power Query incorrectly guesses comma as delimiter
(2:42) Power Query make column wider
(2:55) Power Query removing Continue reading “Excel Power Query Import And Clean Fixed Width Text Files 2359” →

Excel Average Of Non-Zero Rows In Pivot Table – 2358

Posted on by Annie D

There are 7 pivot table secrets in this video, and you haven’t seen six of them. The question: I have several projects in a pivot table. At the bottom, as part of the pivot table, I want an average of the non-zero rows.

The person is currently using AVERAGEIF outside of the pivot table, but that formula must be adjusted.

Today, we use a Data Model pivot table and a DAX Measure to calculate the average. The resulting pivot table has too many rows, so I use a new Set based on Rows.

I can not post the final formula here Continue reading “Excel Average Of Non-Zero Rows In Pivot Table – 2358” →