Category: Business

Calculating Averages Per Weekdays or Weekends Using DAX In Power BI [2023 Update]

Posted on by Annie D

The AVERAGEX function in DAX is used to calculate the average of a set of numeric values in a table or expression that results in a table. It is useful in Power BI reports when you need to calculate an average for a group of values based on a specific condition, such as average sales per customer.

To maximize the function, use it with a filtered table or expression, consider using it with other DAX functions, and be mindful of performance with large data sets. This also showcases how adaptable iterating formulas are in DAX. In this video, Sam Continue reading “Calculating Averages Per Weekdays or Weekends Using DAX In Power BI [2023 Update]” →

Referencing an Excel Sheet With An Apostrophe in its Name – Solved!

Posted on by Bb

The Problem – Apostrophes in Sheet Names

You are trying to use a formula to reference an Excel Sheet with an apostrophe in its name and you keep receiving an error message or a #REF! reference error.

The Solution (Very Easy!)

Great news! Simply replace the apostrophe with two apostrophes in your formula. That’s right, two consecutive apostrophes, not a quote. Very simple solution indeed!

Below I provide two examples. You can manually update the name in your formula (or in the cell you are referencing if using an indirect lookup), or if working with many sheet names, you may opt to use the substitute solution shown below (formula displayed in E5).

The substitute can be nested in an indirect formula also.

Excel Sheet with an apostrophe solution
Excel Sheet With An Apostrophe Solution

Please comment below on any other challenges you would like for me to cover. I am contemplating doing a quick solution series in this format!

Also, be sure to check out Dose for Excel (click the image below)! Add over 100 functions to Excel to increase your productivity and more!  Disclosure: this is an affiliate link, so I may earn a small commission if you decide to purchase the add-in. Thanks for supporting my blog!

Dose for Excel - +100 Functions

Some topics addressed in this post:
Ref Error in lookup
#REF! Error in lookup
Ref Error in formula
#REF! Error in formula
How to fix ref error in Excel from apostrophe
Apostrophe in Excel sheet name issue



How To Multiply In Excel 2584

Posted on by Annie D

How to Multiply in Excel. This video covers eight examples:
Multiply Symbol is * not X.
Multiply Column by Number.
Multiply by Percent.
Multiply two Columns and Copy Down.
Multiply by Same One Cell Reference.
Multiply 3 Cells.
Multiply Many Cells.
Two Columns and Sum.

Table of Contents
(0:00) Intro
(0:08) Multiply Symbol is * not X.
(0:12) Multiply Column by Number.
(0:24) Multiply by Percent.
(0:32) Multiply two Columns and Copy Down.
(0:42) Multiply by Same One Cell Reference.
(0:57) Multiply 3 Cells.
(1:06) Multiply Many Cells.
(1:17) Two Columns and Sum.

This video answers all of these common search terms:
What Symbol Is Multiply In Excel?
How To Type Multiply Symbol In Excel?
How To Multiply 1 Cell In Excel Continue reading “How To Multiply In Excel 2584” →

Setup a Reverse Hybrid Table without using Tabular Editor for Power BI!

Posted on by Annie D

Hybrid Tables can be very useful for mixing archived data with current data. Pat Mahoney joins Patrick to show how to configure a Hybrid Table within a Power BI dataset without using Tabular Editor. Just some straight “M”agic.

Hybrid Table Documentation:
https://learn.microsoft.com/power-bi/connect-data/service-dataset-modes-understand#hybrid-tables

Connect with Pat Mahoney:
https://twitter.com/mahoneypa
https://www.linkedin.com/in/patrick-mahoney-3138526/
https://www.youtube.com/@hoosierbi5669
https://hoosierbi.com/

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

Convert ISO Dates to Dates with Power Query: Excel or Power BI. #Short Excel Magic Trick 07

Posted on by Annie D

Using Power Query, learn how to convert ISO Dates into Dates.
#excel #excelisfun #analytics #microsoftexcel #microsoftmvp, #short #shorts #excelmvp #excelformulas #excelfunctions #excelformulaforjobinterview #excelforbegginers #excelshorts #exceltutorial #exceltips #exceltipstricks #freeclass #freecourse #freeclasses #freeexcelcourse #freeexcel #powerquery #powerbi #powerbidesktop #powerbishorts

How To Create A Word Cloud Of Any Shape In Power BI Using Python

Posted on by Annie D

It’s a handy skill for data analysts to know how to create a word cloud in Power BI because it is a powerful way to visually represent textual data. Word clouds are useful in summarizing large amounts of text data and identifying patterns in it. They are also an effective way to communicate insights to stakeholders who may not have technical expertise.

Word clouds work best with data that consists of short, descriptive phrases or keywords, such as survey responses, customer feedback, or social media posts. In this tutorial, Gaelim is going to show how you can create a word Continue reading “How To Create A Word Cloud Of Any Shape In Power BI Using Python” →

Excel Copy VBA Module to Different Workbook

Posted on by Annie D

Microsoft Excel – Copying VBA Module from one workbook to another.
John B has a workbook with a lot of data.
He created a new workbook with a macro for testing. Now that the macro is working, he wants to copy the working macro from the test workbook to the workbook with all of his data.
This video shows how to launch the VBA Editor with Alt+F11
Display the Project Explorer.
Drag the module from one workbook to another.

Remember to Save As the workbook as XLSB or XLSM. If you save the workbook as XLSX, your macros will be removed.

Topics:
How to copy Excel VBA?

Continue reading “Excel Copy VBA Module to Different Workbook” →

Convert CSV to Parquet using pySpark in Azure Synapse Analytics

Posted on by Annie D

You’ve got a bunch of CSV files and you’ve heard of Parquet. How do you convert them for Azure Synapse Analytics? Patrick shows you how using pySpark.

pyspark DataFrame
https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/dataframe.html

pyspark.sql.DataFrameReader.load
https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/api/pyspark.sql.DataFrameReader.load.html

pyspark.sql.DataFrameWriter
https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/api/pyspark.sql.DataFrameWriter.html

📢 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***
🛠 Continue reading “Convert CSV to Parquet using pySpark in Azure Synapse Analytics” →

How To Use Variables In DAX Formula With Power BI [2023 Update]

Posted on by Annie D

Learning how to use variables in DAX formulas is beneficial for data analysts because it allows them to simplify their code and make it easier to read and understand. Variables can be used to store intermediate results, making complex calculations more manageable and reducing the risk of errors. Additionally, using variables can help to optimize query performance, as calculations that are reused multiple times can be cached in memory.

Using variables can make it easier to debug code, as it allows analysts to isolate and test individual parts of the calculation. In this tutorial, Sam will demonstrate how you can Continue reading “How To Use Variables In DAX Formula With Power BI [2023 Update]” →

Remove Page Watermark From Excel – 2582

Posted on by Annie D

How to get rid of a watermark in Excel? There are three different types of watermarks that are common in Excel. This video discusses all three.
Table of Contents
(0:00) Remove Watermark from Excel
(0:12) Remove Page Watermark
(0:33) Removing Watermark from Header
(1:06) Visible Watermark that does not print

This video answers these common search terms:
excel how to get rid of watermarks
how do i remove a watermark in excel
how do you remove a watermark from excel?
how do you remove a watermark in excel
how do you remove watermark in excel
how to get rid of watermark excel
how to get rid of watermark in excel
how to get rid Continue reading “Remove Page Watermark From Excel – 2582” →

How to Get the Sum At the Top of Excel #shorts

Posted on by Annie D

How to get the sum at the top of Excel?
This short video shows two methods:
Add the sum at the bottom and then Cut using Ctrl+X and paste at the top.
Or, start at the top, click AutoSum, and then select the range of numbers with the mouse.

How To Get Sum At Top Of Excel
How To Sum At The Top In Excel

Excel Duplicates – Mark or Remove – 5 Answers in 55 Seconds – #shorts

Posted on by Annie D

5 Excel topics in 55 seconds. In this video, I answer five common questions about duplicates in Excel. This video will address:
excel find duplicates without deleting
can excel find duplicates
excel duplicates find
can i find duplicates in excel
excel remove duplicates without losing data
excel find duplicates in a list
can excel highlight duplicates
excel duplicates highlight
can excel look for duplicates
can excel pick up duplicates
excel recognize duplicates
how to see if duplicates in excel
how to see duplicates in excel column
excel check for duplicates in a list
excel duplicate check
excel check if duplicates in range
excel for duplicates
excel duplicates in one column
excel duplicates color
excel and duplicates
excel duplicates conditional formatting
excel duplicate Continue reading “Excel Duplicates – Mark or Remove – 5 Answers in 55 Seconds – #shorts” →