Category: Power Query

Writing your first SQL Statement!

Posted on by Annie D

New to writing T-SQL? Patrick walks you through your first SQL statement. Great foundation to have when working with SQL Server or Azure Synapse Analytics!

Download SQL Server Management Studio
https://docs.microsoft.com/sql/ssms/download-sql-server-management-studio-ssms

Download and install Azure Data Studio
https://docs.microsoft.com/sql/azure-data-studio/download-azure-data-studio

Transact-SQL Reference (Database Engine)
https://docs.microsoft.com/sql/t-sql/language-reference?view=sql-server-ver16

Learn how you can become an Azure Synapse Influencer: http://aka.ms/synapseinfluencers

📢 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 #GuyInACube

REPLAY Power BI tips from the Pros – LIVE (Mar 19, 2022)

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 “REPLAY Power BI tips from the Pros – LIVE (Mar 19, 2022)” →

Excel To Schedule Prison Haircuts Only On Saturday – 2484

Posted on by Annie D

This video is all about the Excel function for WORKDAY.INTL. It allows you to make sure that you are always scheduling an event to occur on a Saturday, or Tues/Thurs or Friday.
The secret here is the alternate syntax for the Weekend argument.
Table of Contents
(0:00) Find a particular Saturday in Excel
(0:25) WORKDAY introduction
(1:37) Find a particular Saturday in Excel
(2:32) Find the next Tuesday
(5:02) Find next Tuesday or Thursday
(5:46) Handling holidays
(6:58) Wrap-up

Reduce the queries being sent with Incremental Refresh in Power BI!

Posted on by Annie D

Have you noticed some extra queries being sent when using Incremental Refresh in Power BI? Patrick explains why this could happen and a potential workaround.

Privacy Levels:
https://docs.microsoft.com/power-bi/enterprise/desktop-privacy-levels

📢 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 “Reduce the queries being sent with Incremental Refresh in Power BI!” →

Exploring the preview of datamart in Power BI!

Posted on by Annie D

We take a look at the newly announced preview of datamart in Power BI! A no code experience to build a relational database for analytics right inside Power BI!

Announcement Blog: https://powerbi.microsoft.com/blog/announcing-public-preview-of-datamart-in-power-bi/

Documentation: https://docs.microsoft.com/power-bi/transform-model/datamarts/datamarts-overview

📢 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 #datamart #GuyInACube

Getting started with Azure Synapse Analytics

Posted on by Annie D

Not sure where to begin with Azure Synapse Analytics? Patrick gets you started and teaches you the clicks to get up and running quickly!

Get Started with Azure Synapse Analytics
https://docs.microsoft.com/azure/synapse-analytics/get-started

Learn how you can become an Azure Synapse Influencer: http://aka.ms/synapseinfluencers

📢 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 “Getting started with Azure Synapse Analytics” →

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

A new way to manage gateways in Power BI!!!

Posted on by Annie D

We now have a new way to manage gateways within Power BI and it is a much-welcomed update! Adam walks you through the experience to get you ready for it!

New data source and gateway management experience
https://powerbi.microsoft.com/blog/new-data-source-and-gateway-management-experience/

📢 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 “A new way to manage gateways in Power BI!!!” →

Excel XLOOKUP Return Non Adjacent Columns – 2488

Posted on by Annie D

Another question from the UCF Accounting Conference. JoAnne asks… XLOOKUP can return multiple columns. What if you want to return several non-adjacent columns, for example, January, April, July, October?
There are three solutions presented here.
Table of Contents
(0:00) XLOOKUP return non-adjacent columns
(0:30) XLOOKUP with FILTER
(2:39) Using HSTACK
(3:17) VLOOKUP with Ctrl+Shift+Enter
(4:40) How would you solve it?

Another 30-Day Query Folding challenge!!!

Posted on by Annie D

Here is another 30-day Query Folding challenge video for you. Patrick takes on Day 7 and ramps up his Power Query knowledge! Will Power BI Desktop get the best of him???

GitHub Repot: https://github.com/itsnotaboutthecell/powerquerym/tree/master/%2330DQUERY

📢 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 Continue reading “Another 30-Day Query Folding challenge!!!” →

Are you following best practices with Azure Synapse Analytics?

Posted on by Annie D

How do you know if you are following best practices for Azure Synapse Analytics? One way is to check out this report that Patrick walks through. It’s a great way to get an understanding where your database is at!

Azure Synapse Analyzer Report to monitor and improve Azure Synapse SQL Dedicated Pool performance
https://techcommunity.microsoft.com/t5/azure-synapse-analytics-blog/azure-synapse-analyzer-report-to-monitor-and-improve-azure/ba-p/3276960

Download Location:
https://github.com/microsoft/synapse-analyzer/tree/main/Synapse%20BPA%20Report

Maximizing rowgroup quality for columnstore indexes in dedicated SQL pool
https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-memory-optimizations-for-columnstore-compression

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

🎓 Continue reading “Are you following best practices with Azure Synapse Analytics?” →

Sorted Summary Report WIth Totals In Excel Without A Pivot Table – 2487

Posted on by Annie D

A question from Elijah at the UCF Accounting Conference. I was showing how to use the UNIQUE function to get a list of products. Elijah asked if I could then get the total sales for each product. Plus a heading row and a total row. With the items sorted high to low.
The final result uses several new Excel functions: UNIQUE, SUMIFS, HSTACK, SORT, VSTACK, and LET.
This lets you create something that is like a pivot table, but there is no need to refresh as the data changes.
Table of Contents
(0:00) Welcome
(0:16) Formulas to Values
(1:08) UNIQUE list of products
(1:35) SUMIFS by Continue reading “Sorted Summary Report WIth Totals In Excel Without A Pivot Table – 2487” →