From the May 2023 issue of Strategic Finance magazine, how to use TEXTSPLIT in Excel.
Category: Power Query
Excel Challenge: Most Efficient Way To REPTARRAY – 2585
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…
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…”
How To Multiply In Excel 2584
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!
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
Excel Copy VBA Module to Different Workbook
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
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”
Remove Page Watermark From Excel – 2582
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
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
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”
Why Excel SUM is Zero #Shorts
Why Excel SUM is zero.
Why Excel Sum function not working.
Why Excel SUM returns 0.
Why Excel Sum is wrong.
Why Excel Sum shows 0.
Why Excel Not Showing Sum.
Why Excel Sum Formula Not working.
Do you have a column of numbers that won’t sum in Excel? It is most likely that the numbers have been stored as text. Here are two quick ways to convert those text numbers and the numbers will then sum.
One frustrating behavior when this happens: =SUM(A2:A4) is zero, but =A2+A3+A4 calculates correctly. That is because the plus sign coerces a number out of the text, but the SUM function is Continue reading “Why Excel SUM is Zero #Shorts”
Excel You CAN Change The Font Size In The Formula Bar – #shorts
Why is the text in the formula bar so small? If you are on a Zoom call or doing a presentation, it is impossible for the audience to see. There is an amazing trick for making the text in the Excel formula bar HUGE. Try this out.
Microsoft Excel Formula Bar Font Size