Category: Business

Power Query UnPivot to Convert Cross Tabulated Table into Proper Data Set. 365 MECS Class 14

Posted on by Annie D

Download Zipped Folder with All Files: https://excelisfun.net/files/14-M365ExcelClassVideoFiles.zip
This video teaches how to unpivot a cross tabulated table and convert it to a proper table so you can perform data analysis.
This full free Microsoft 365 Excel & Power BI class is taught by Excel MVP and Highline College Professor and can be found here: https://www.youtube.com/playlist?list=PLrRPvpgDmw0nre_bTeBfJWjrnixKoyNtW
This video covers.
1. (00:00) Introduction and video topics
2. (00:25) What is a Cross Tabulated Table and What are Benefits of Converting to Proper Table?
3. (01:14) UnPivot feature in Power Query.
4. (01:52) Table.UnpivotOtherColumns M Code Function
5. (02:21) Sort with Resulting Table
6. (02:32) PivotTable Continue reading “Power Query UnPivot to Convert Cross Tabulated Table into Proper Data Set. 365 MECS Class 14” →

How To Create Deneb IBCS-style Performance Visual In Power BI

Posted on by Annie D

Using IBCS-style performance visuals in Power BI can improve communication by using clear and concise visualizations to convey performance data, making it easier for viewers to understand key metrics and identify trends. This can lead to more informed decision-making and better business outcomes. In this tutorial, Greg shows how to create IBCS-style Performance Visual using Deneb/Vega-Lite in Power BI.

*****Video Details*****
00:00 Introduction
00:50 IBCS features
02:00 Target visual
03:38 Current Year vs Previous Year
10:50 Variance amounts
24:06 Variance percent
29:20 Legend
31:40 Final visual
41:20 Summary

*****Related Links*****
Enterprise DNA Deneb Showcases – https://forum.enterprisedna.co/c/deneb-showcase/58​
IBCS-style Performance Visual DENEB Template and Power BI file – rel=”nofollow”>https://forum.enterprisedna.co/t/deneb-template-ibcs-performance-visual/31534​
DENEB website – https://deneb-viz.github.io/​
Vega-Lite website – https://vega.github.io/vega-lite/​
IBCS website – https://www.ibcs.com/

***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI – www.enterprisedna.co/courses/ultimate-beginners-guide-to-power-bi
FREE COURSE – Ultimate Beginners Guide To DAX – www.enterprisedna.co/courses/ultimate-beginners-guide-to-dax
FREE – Power BI Resources – www.enterprisedna.co/power-bi-resources
Enterprise DNA On-Demand – app.enterprisedna.co
Enterprise DNA Subscription – app.enterprisedna.co/pricing
Enterprise DNA Events – www.enterprisedna.co/events

#EnterpriseDNA #Deneb #DenebTutorial #VegaLite #VegaLiteTutorial #IBCS #PowerBI #PowerBIDesktop #PowerBITutorial

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

Start Learning T-SQL for Azure Database and Azure Synapse

Posted on by Annie D

Starting your journey to learn SQL? Adam gives you some resources to quickly ramp up with T-SQL for Azure SQL Database and Azure Synapse Analytics!

SQL Standard
https://en.wikipedia.org/wiki/SQL#Standardization_history/

Procedure Extensions – Different SQL bases out there
https://en.wikipedia.org/wiki/SQL#Procedural_extensions

Get Started Querying with Transact-SQL – Learning Path
https://learn.microsoft.com/training/paths/get-started-querying-with-transact-sql/

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

T-SQL Fundamentals (Developer Reference) – Itzik Ben-Gan
https://amzn.to/3Jd2yHQ

Learn T-SQL Querying: A guide to developing efficient and elegant T-SQL code
Pedro Lopes / Pam Lahoud
https://amzn.to/3mriUUm

Brent Ozar Unlimited
https://www.brentozar.com/

StackExchange – Questions
https://stackexchange.com

📢 Become a member: https://guyinacu.be/membership

*******************

Want Continue reading “Start Learning T-SQL for Azure Database and Azure Synapse” →

Creating Dynamic Outlier Trigger Points – Advanced Power BI Analysis Technique [2023]

Posted on by Annie D

Creating outlier trigger points dynamically in Power BI provides increased flexibility in adjusting the threshold for identifying outliers based on different scenarios and business needs, as opposed to using a static number. This allows for more accurate and relevant outlier detection, which can lead to better insights and decision-making. In this tutorial, Sam will demonstrate how you can create dynamic outlier trigger points in Power BI.

VIEW FULL WORKSHOP HERE – https://www.youtube.com/live/uG32PtbWfTI?feature=share

*****Video Details*****
00:00 Introduction
02:23 The goal
03:05 Sales grouping
03:30 Dynamic non-outlier sales
04:20 Parameters
06:24 Changing static formulas to dynamic
07:20 Displaying the dynamic outliers

***** Learning Power BI? *****
FREE COURSE – Continue reading “Creating Dynamic Outlier Trigger Points – Advanced Power BI Analysis Technique [2023]” →

Top 10 Power BI Drill Though Pro Tips

Posted on by Annie D

Power BI Drill Through allows users to focus on specific details within a report by navigating to related information in a separate page or report, which helps in identifying patterns and making informed decisions. This interactivity and flexibility empowers users to analyze data more effectively and gain deeper insights.

Typically, what you see in Drill Through is a table and drill through to a detailed page. In this video, Brian shares how you can drill through other constructs of your report, and his tips on how to maximize this underrated aspect of Power BI.

*****Video Details*****
00:00 Introduction
01:59 Where to get the images
02:25 Continue reading “Top 10 Power BI Drill Though Pro Tips” →

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.

Consolas Font For Excel 2362

Posted on by Annie D

What is your favorite font in Excel? Anna and Lisa showed me the Consolas font. It is great because you can tell zero from O and one from lower case L.

Table of Contents
(0:00) Axel font for Excel
(0:19) Typewriters without a 1 key
(0:54) People who type L instead of 1
(2:04) How I met ExcelisFun
(2:37) Make a custom Office Theme with Consolas font
(3:32) Applying a custom office theme
(3:45) Making a theme be default
(4:09) Default Font in Excel Options

Here Are The Differences Between Connectivity, Dataset and Storage Modes In Power BI

Posted on by Annie D

Although this might seem basic, there seems to be some confusion between Connectivity Modes, Dataset Modes, and Storage Modes in Power BI. They’re all kind of related but they refer to different aspects of data usage.

In this tutorial, Greg is going to give a fundamental 101 and try to distinguish from one another the Connectivity Modes, Dataset Modes, and Storage Modes in Power BI. Understanding these modes can help users make informed decisions when selecting the appropriate options to optimize data usage and improve the performance of their reports and dashboards.

*****Video Details*****
00:00 Introduction
00:32 Usual misconceptions
01:13 Activating Direct Query
02:04 Connectivity Continue reading “Here Are The Differences Between Connectivity, Dataset and Storage Modes In Power BI” →

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

Add YTD To An Excel Pivot Table – 2385

Posted on by Annie D

Diane has a fairly complex pivot table. She is using GETPIVOTDATA to pull data from the pivot tables to a summary report in another workbook. In another section of the report, she needs to pull YTD. But this complicates the formula, because she has to add an expanding range as the months continue.
I mentioned that she could create a second pivot table that shows the sales numbers as YTD numbers. This video shows how to copy the first pivot table and how to use Running Total In to convert from monthly sales to YTD Sales.