Month: February 2023

Excel Sum The Comma Delimited Numbers From One Cell – 2372b

Posted on by Annie D

Om asks: I have 123,413,413,1352,244­ in a single cell.
There are many numbers with commas in between in each cell.
How to Sum all the numbers using a formula?
I don’t want to use Text to Column.
By the way, it is Excel 2010.

I use Brad Yundt’s three-line VBA function to split the numbers.
Then Excel formulas to sum them.
Also, an easier formula if you have Microsoft 365.

Table of Contents
(0:00) Sum all comma-delimited numbers in cell using an Excel formula
(0:19) Easy solution with TEXTSPLIT
(0:32) Switch to VBA for Brad Yundt Splitter code
(0:55) Using SPLITTER custom function in Excel
(1:25) Wrap up with Wally & Nancy

How To Easily Create And Organize Multiple Measures At Once In Power BI

Posted on by Annie D

In our past video, Brian showed how to automatically kill unused columns and measures in Power BI. Now, he’ll demonstrate some cool time-saving techniques on how you can create and organize dozens of measures at once, while also setting format strings and turning off aggregations. 

*****Video Details*****
00:00 Introduction
01:04 Data model
01:50 C# script
02:18 Fact table
03:24 Measures table
04:00 Metadata Mechanic Pro

*****Related Links*****

Tabular Editor 3:
https://tabulareditor.com/

Tabular Editor 2: (Free)
https://github.com/TabularEditor/TabularEditor
Metadata Mechanic Pro (part of Enterprise DNA Power Tools Suite)
https://www.enterprisedna.co/tech/power-tools/

Metadata Mechanic (Free)
https://github.com/gdeckler/MicrosoftHatesGregsQuickMeasures
GitHub Gist Bulk Measure Creation Script
https://gist.github.com/bjulius/8e71fde3e82574d278b9b06093583905

GitHub Gist Countrows Measure Continue reading “How To Easily Create And Organize Multiple Measures At Once In Power BI” →

Power Query Insert Blank Row After Each Group – 2372

Posted on by Annie D

Back in episode 2359, I needed to add a blank row after every group of names. Although I was already in Power Query, I went out to Excel to add the blank rows.

Several people commented with code to add a blank row after each group in Power Query. Thanks to @BillSzysz1 @radosawpoprawskiyourfriend769 @GeertDelmulle Rico S and Suat Ozgur for guiding me on how to add one line of M code to my query.

I am at that stage in my Power Query journey that I prefer to do everything in the Power Query editor. Once I switch over to the advanced Continue reading “Power Query Insert Blank Row After Each Group – 2372” →

How To Get Totals Correct When Using Advanced DAX Logic In Power BI [2023 Update]

Posted on by Annie D

When you start writing more advanced DAX formula, your totals will likely start playing up and not returning the values you want. In this tutorial, Sam demonstrates how to use a technique that will get you the correct totals particularly when using advanced DAX logic.

*****Video Details*****
00:00 Introduction
01:11 The objective
01:45 Total sales
02:15 Monthly averages
03:37 Average yearly result
05:24 Incorporating HASONEVALUE

***** 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 Continue reading “How To Get Totals Correct When Using Advanced DAX Logic In Power BI [2023 Update]” →

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

Excel Count Super Bowl Squares By Name – 2371

Posted on by Annie D

A question from @chwang06 : Asks how to count Super Bowl Squares by the name in the squares.
In this video, I will show how to get a list of players with =UNIQUE(SORT(TOCOL(C3:L12))) and then count how many times each person played using =COUNTIFS(C3:L12,C17#)

Table of Contents
(0:00) Question: Count Excel Super Bowl Squares by Name
(0:35) Make sure your Excel has TOCOL function or use Excel Online
(0:55) Excel.New in Excel Online
(1:05) Unwinding Excel range to 1 column vector
(1:33) SORT and UNIQUE functions
(2:00) COUNTIFS with Dynamic Range as second argument
(2:30) Many names for # symbol
(2:51) Mention video 2385
(3:00) Wrap up with Wally Continue reading “Excel Count Super Bowl Squares By Name – 2371” →

Excel Statistical Analysis for Business – Busn 210 – Winter 2023 Quarter Introductory Video

Posted on by Annie D

Busn 210, Excel Statistics for Business class taught by Excel MVP Mike Girvin at Highline College.
This is the intro video for matriculating students at Highline College in winter quarter, 2023.
To watch at YouTube here is the playlist: https://www.youtube.com/playlist?list=PLrRPvpgDmw0m3oqpp1XcPuaxyM4Bpi0dN

Busn 218 – Advanced Excel – Winter 2023 Quarter Introductory Video

Posted on by Annie D

Busn 218, Advanced Excel class taught by Excel MVP Mike Girvin at Highline College.
This is the intro video for matriculating students at Highline College in winter quarter, 2023.
To watch at YouTube here is the playlist:
https://www.youtube.com/playlist?list=PLrRPvpgDmw0nre_bTeBfJWjrnixKoyNtW

Guy in a Cube answers the web’s most searched questions on Power BI

Posted on by Annie D

Let’s see what the most asked questions are on the web about Power BI! Adam looks at google for the questions and then responds. You heard that right! He went to Google.

Power BI forecasting
https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-analytics-pane#apply-forecasting

Describing the forecasting models in Power View
https://powerbi.microsoft.com/en-us/blog/describing-the-forecasting-models-in-power-view/

Power BI on a Mac?
https://www.youtube.com/watch?v=qUpqtWEOG40

Understand star schema and the importance for Power BI
https://learn.microsoft.com/en-us/power-bi/guidance/star-schema

📢 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: <a href="https://guyinacu.be/courses" Continue reading “Guy in a Cube answers the web’s most searched questions on Power BI” →