Month: April 2023

Auto Numbers in an Excel Table with a Formula. #Short Excel Magic Trick 08

Posted on by Annie D

Learn how to create an auto number for records in an Excel Table using a formula.
Download Excel File: https://excelisfun.net/files/SEMT0008-12.xlsx
#excel #excelisfun #analytics #microsoftexcel #microsoftmvp, #short #shorts #excelmvp #excelformulas #excelfunctions #excelformulaforjobinterview #excelforbegginers #excelshorts #exceltutorial #exceltips #exceltipstricks #freeclass #freecourse #freeclasses #freeexcelcourse #freeexcel
#exceltable #sequencefunction #sequence #increment #indexfunction #structured #nomenclature

Excel Challenge: Most Efficient Way To REPTARRAY – 2585

Posted on by Annie D

Dave wishes that Excel would offer a REPTARRAY function, just like REPT but for arrays. This video shows four different ways to repeat an array N times.

Vote for Microsoft to add REPTARRAY:
https://feedbackportal.microsoft.com/feedback/idea/0f6d6e22-7cb3-ed11-a81b-002248519701

Details about FastExcel: http://mrx.cl/speedv4

Here are the formulas used in the video:
=TEXTSPLIT(REPT(TEXTJOIN(“,”,TRUE,A)&”,”,$E$1),,”,”,TRUE)
=DROP(REDUCE(“”,SEQUENCE($E$1),LAMBDA(a,b,VSTACK(a,D))),1)
=LET(a,E,rw,COUNTA(a),MAKEARRAY(rw*$E$1,1,LAMBDA(r,c,CHOOSEROWS(a,MOD(r-1,rw)+1))))
If you have FastExcel V4 from Charles Williams: =REPEAT(A2#,E1)

If you have a better way, please post below in the YouTube comments.

Excel Most Efficient Way to REPTARRAY Challenge
(0:00) How to REPTARRAY
(0:51) JOIN, REPT, then SPLIT
(1:52) REDUCE, SEQUENCE, LAMBDA, VSTACK
(2:51) MAKEARRAY, LAMBDA, CHOOSEROWS, MOD
(3:56) FAST EXCEL V4
(4:56) Vote for REPTARRAY
(5:42) Nancy Faust

Where are the Power BI Gateway logs? I thought I knew…

Posted on by Annie D

Do you know where the Power BI Gateway log files are? I didn’t! I’ll show you a tool I use to help figure something like this out. And also tell you where they are!

Process Monitor
https://learn.microsoft.com/sysinternals/downloads/procmon

Rui Romano’s Gateway Monitor Report
https://github.com/RuiRomano/pbigtwmonitor

📢 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 “Where are the Power BI Gateway logs? I thought I knew…” →

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

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” →

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

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]” →