Can someone please help me???
I have an Excel file that has the cost column in cents so if something is 1.25 it is showing as 125 .
When I change it to currency the numbers change to 125.00….
Anyone knows how to fix the issue faster than one by one. I have over 5,000 items to go through!
In this short video, learn how to multiply an entire column by 0.01 to quickly convert 125 cents to 1 dollar and twenty five cents.
Download Excel File: https://excelisfun.net/files/EMT1800.xlsx
Learn how to show all formulas in a column using a single formula.
Topics:
1. (00:00) Introduction
2. (00:17) Show all formulas in a column using a single formula using TOCOL, ADDRESS, ROW, COLUMN and FORMULATEXT functions
3. (03:30) Summary, Closing, Video Links
We tell you to push your transformations upstream as much as possible. But you don’t know how to write SQL. That’s ok, let Power Query do it for you to move your project forward. Patrick shows you how!
📢 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 #GuyInACube
In this video, Melissa will demonstrate an approach to Access Analytic’s Start Stop Challenge. The ask is to calculate the total number of hours member for a staff member on each month and date. See how you can solve this challenge!
Start Stop Challenge: https://accessanalytic.com.au/power-query-stop-start-challenge
***** Video Details *****
00:00 Introduction
00:55 Dataset
01:11 Filtered rows
03:10 Adding custom column
05:12 Variables
15:30 Projection
16:09 Aggregate values
***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI – http://portal.enterprisedna.co/p/ultimate-beginners-guide-to-power-bi
FREE COURSE – Ultimate Beginners Guide To DAX – http://portal.enterprisedna.co/p/ultimate-beginners-guide-to-dax
FREE – Power BI Resources – <a Continue reading “Taking On Access Analytic’s Start Stop Challenge”
Using PySpark in Azure Synapse Analytics and found that my regular way of concatenating two strings in T-SQL didn’t work. I figured out what you need to do to make this work.
📢 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 Continue reading “Overcoming a concatenation challenge in Azure Synapse”
In this tutorial, Sam deep dives onto the key things to think about and implement when using ‘what if’ parameters in Power BI. There are certain tips and techniques you can utilize that will enable a prediction type analysis with Power BI. You’ll be amazed at what you can achieve when you set up your models and calculations correctly for this type of analysis.
***** Video Details *****
00:00 Introduction
03:05 Creating a sample Demand Scenario
03:30 Creating a sample Pricing Scenario
05:01 Using Iterating functions in What If analysis
07:25 Bookmarking scenarios
***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI Continue reading “Getting Started With ‘What If’ Parameters – Scenario Analysis Techniques [2022 Update]”
In Excel… How does one create a character+number THAT ONLY UPDATES WHEN ENTERED and DOES NOT change with F9 or reloading the spreadsheet?
I want to create sort of an inventory number that I can put on my 3D printer filament boxes to identify them quickly.
But I don’t want the inventory number to ever change after the first creation of the numbers/characters.
This video answers these questions:
Excel random not change?
Excel random keeps changing?
why does random number change in Excel?
Excel random number without changing?
how to stop Excel random number changing?
can i generate random numbers in Excel?
Excel and random number generator?
Excel rand Continue reading “Excel Enter Random Numbers That Will Not Change Again – #shorts”
In this tutorial, Gaelim is going to show how you can break down time series data into essential components. Using a time series decomposition method will help you find a better way to describe data when you try to describe trends, seasonality or unexpected events, and is a great stepping stone for forecasting.
***** Video Details *****
00:00 Introduction
00:45 Types of graphs
02:50 Dataset
03:15 Python script
05:30 Seasonal decompose
05:54 Creating visual within data
06:40 Seasonality
08:46 Residual
09:14 Not resetting index
10:08 Trend
***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI – http://portal.enterprisedna.co/p/ultimate-beginners-guide-to-power-bi
FREE COURSE – Ultimate Beginners Guide To Continue reading “How To Easily Decompose Time Series Data In Power BI And Python”
Download Excel File: https://excelisfun.net/files/EMT1799.xlsx
Learn how to filter records based on a list of values using Excel worksheet formula.
Topics:
1. (00:00) Introduction
2. (00:26) Filter records based on a list of values using FILTER, XMATCH and ISNUMBER functions.
3. (02:22) VSTACK function to Filter records based on one or more lists from any were on worksheet.
4. (03:19) TOCOL function to Filter records based on one or more lists that are listed sequentially.
5. (04:09) Summary, Closing, Video Links
Excel getting the unique values going across a row.
The Remove Duplicates command in Excel always removes rows. What if you want to remove duplicates that appear in the same row? The UNIQUE function offers a By Column option.
Also in this video: Sorting sideways in Excel using the Left-to-Right Sort option.
What happens if you leave off the optional By_Col argument in UNIQUE.
Why UNIQUE returns the original range.
Table of Contents
(0:00) Sort or Unique sideways in Excel
(0:37) UNIQUE by Column
(1:48) Nested arrays not supported
(2:00) Sorting sideways in Excel
(2:38) UNIQUE of rectangular range
(3:33) Why UNIQUE returns original range
(4:15) BYCOL defaults to False
(4:35) UNIQUE Continue reading “Excel Unique Across Each Individual Row – 2519”
Stevenson asks: Why won’t this SUMIFS work inside of a LET function in Excel?
It turns out that he is trying to do a calculation in the first argument of the SUMIFS.
This is against the rules.
If you would have tried it without the LET, Excel would have given you the mysterious error of “There’s a Problem With This Formula”.
When you try to do it inside of LET, the formula parser is not smart enough to refuse the formula, but you end up with a bunch of #VALUE! errors in Excel.
The same limitation applies to SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS, MINIFS, SUMIF, AVERAGEIF, Continue reading “Excel Why Wont This SUMIFS Work Inside Of Let 2518”
Being an effective Power BI data analyst involves supporting different functions within organizations. Learn vital technical skills that a Power BI data analyst should have and ace the Microsoft Power BI Data Analyst PL-300 exam with a complete training guide in our newest course!
Watch as Brian introduces this new course developed by long-time Microsoft certified trainer and recently awarded Microsoft Innovative Educator, Ilgar Zarbaliyev. Learn what are the benefits in getting certified, how the course is structured, and what you can take away from this new course.
Know more here: https://www.enterprisedna.co/upcoming-courses/
***** Video Details *****
00:00 Introduction
01:24 Igar’s background
03:29 Benefits of certification
06:25 Continue reading “Microsoft PL-300 Certification – New Course!”