Excel: Importing data from an image

Recently, I’ve been getting mail that contains data that I need to get off of the paper and into Excel. Typing the data into Excel is one option. But I figured there must be better ways.

In this video I show 2 ways to get data by taking a picture, then converting the text from the data into something that Excel can use. You’ll see:
– Save the image in Microsoft Word as a PDF
– Import the image into OneNote, then use Extract Text from Image.

NOTE: All methods involve some kind of clean-up. The trick is to figure out which method results in the least mess.

0:00 Intro
1:55 A comment about the Excel phone app
2:20 Import work schedule
4:37 Import workshop data
7:16 Import from a magazine page
8:35 Why not import PDF using Power Query?
9:10 Outro

For a list of my Excel courses at Lynda/LinkedIn:
https://www.linkedin.com/learning/instructors/oz-du-soleil
There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

Power Query: Split by Variable Columns (BONUS: Road Trip Summary)

It’s easy to use Power Query to split by, say, 5 columns. But, what if your data changes and needs to be split by 3 columns or 10 columns? That’s trickier–especially since Power Query will hard-code that 5 in the query and will ignore future changes.

This video shows how to get Power Query to cooperate. It takes a few steps, but we get it done!

Also. You’ll get to see where I’ve been during my time away. I finally went on a road trip that I’d been putting off for 7 years.

#Roadtrip
#SplitVariableColumns
#SplitColumnsinExcel

For a list of my Excel courses at Lynda/LinkedIn:
https://www.linkedin.com/learning/instructors/oz-du-soleil
There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

Power Query Challenge: Split by Multiple Delimiters | Merge All Columns

Iranian Dr. Excel posted a challenge on LinkedIn:
How can you split columns when there are multiple delimiters? Ex:
Joe+Lisa+Francine/Kathy
Rita&Sal&Gene
Samantha/Denise

This was interesting for several reasons:

1. It’s very easy to do if you want a 1-and-done solution with just 3 delimiters. However,
2. If you want something truly dynamic, it involves solving 2 major problems with Power Query: splitting variable columns and merging all columns in a query.
3. I show an ugly but effective solution that I call The Elephant Through the Front Door Move.

First. When splitting columns with Power Query, a value gets hard-coded; e.g., if your initial split goes into 5 columns, the 5 is stuck, and if you later have 7 columns or 3 columns, that 5 is still there.
I found a solution to this here:

Second. Sometimes we want to merge all of the columns in a query, but there’s no feature for that. But here is Power Query M-Code:

This was hard! But the solution I show is truly dynamic. If there are more delimiters in future data, they get picked up; if the number of columns grows or expands, Power Query will cooperate.

For a list of my Excel courses at Lynda/LinkedIn:
https://www.linkedin.com/learning/instructors/oz-du-soleil
There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

Excel NETWORKDAYS part 3: calculating custom weekends with Power Query

In the previous 2 videos we’ve looked at calculating NETWORKDAYS and days off that aren’t just Saturday and Sunday. Thanks for Bill Szysz, Wayne Edmondson, XLarium and others for leading a lively discussion and offering really cool formula-based solutions. (The conversation is on this video: https://youtu.be/njC1AvEU3fg)

In this video I show you something I learned from Wayne and Bill. You can create your custom weekends by putting together a string of 1s and 0s that represent each weekday.

1 = day off
0 = workday
The string starts with Monday.

If you work (or go to school) only on Tuesdays and Wednesdays, your string would look like this:
“1010111”
Thus:

=NETWORKDAYS.INTL(Start_Date, End_Date, “1010111”, [Holidays])

You’ll also see Power Query, replace values and merge columns in order to simplify calculating net work days in Excel.

For a list of my Excel courses at Lynda/LinkedIn:
https://www.linkedin.com/learning/instructors/oz-du-soleil
There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

Calculating NETWORKDAYS when people work just 1 to 4 days/wk or have non-consecutive days off

This video starts with a correction from my previous video. I was wrong about how NETWORKDAYS works and I should have used NETWORKDAYS.INTL because it allows for “weekends” that are other than Saturday-Sunday. Thus, if you work at a restaurant that’s only open Tuesday thru Sunday, NETWORKDAYS.INTL will let you choose Monday as your weekend.

Now, here’s a challenge. What about part-time people who work Mondays, Wednesdays and Fridays? Or! When I was living in California, I worked 4 days, 10 hours each, for a full 40 hours. I loved working Monday, Tuesday, Thursday Friday. I was off on Wednesdays, Saturdays and Sundays.

To calculate the net work days in such situations NETWORKDAYS.INTL can’t help us.

In this video I show how to achieve this by using Power Query (unpivot), FILTER and COUNTIF. I also make clever use of the MATCH function to identify holidays.

#NETWORKDAYS
#3DayWeekends
#FILTERfunction

For a list of my Excel courses at Lynda/LinkedIn:
https://www.linkedin.com/learning/instructors/oz-du-soleil
There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

NETWORKDAYS Function | Calculating Net Work Days in Power Query | Weirdness & Warnings

Santosh asked about NETWORKDAYS and calculating net work days in Power Query. OUCH! This was rough.

NETWORKDAYS is a weird function. If you work at a restaurant that’s open 7 days/wk or a salon that’s closed on Mondays and Tuesdays, NETWORKDAYS makes a mess. However, using Power Query can be complicated with a lot of steps, but it’s more accurate than fiddling around with NETWORKDAYS.

This video has several phases:
0:00 Introduction
2:22 The NETWORKDAYS function
4:46 Calculating Net Work Days in Power Query
12:40 Outro

Download the file:
https://datascopic.net/wp-content/uploads/2021/06/NETWORKDAYS-PQ.xlsx
#NETWORKDAYS
#POWERQUERY
#Anti-Join
For a list of my Excel courses at Lynda/LinkedIn:
https://www.linkedin.com/learning/instructors/oz-du-soleil
There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

Power Query: Handling Percentages & Percent Totals

(NOTE: This replaces a previous video that had audio problems.)

Calculating percentages in Power Query is not as easy as it is in native Excel.

– First, when using the interface to calculate values in 2 different columns, you have to select the columns in the right order.
– Second, If you have a column of amounts and want to get each entry’s percentage of the total, that’s not straightforward. We end up making a parameter of the total and then calculate the percentages using that parameter.

#PowerQuery
#Percentages
#PercentTotals

For a list of my Excel courses at Lynda/LinkedIn:
https://www.linkedin.com/learning/instructors/oz-du-soleil
There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

Power Query: Handling Percentages & Percent Totals

Calculating percentages in Power Query is not as easy as it is in native Excel.

– First, when using the interface to calculate values in 2 different columns, you have to select the columns in the right order.
– Second, If you have a column of amounts and want to get each entry’s percentage of the total, that’s not straightforward. We end up making a parameter of the total and then calculate the percentages using that parameter.

#PowerQuery
#Percentages
#PercentTotals

For a list of my Excel courses at Lynda/LinkedIn:
https://www.linkedin.com/learning/instructors/oz-du-soleil
There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

Identify 2 partial strings of data in 1 cell | Text.Contains | Cross Join | Power Query

Someone asked about identifying more than 1 partial string of text in a cell. Hmmm … interesting.
Well, we go back to our old friends Cross-Join and Text.Combine in Power Query to help us out.

Download the workbook: https://datascopic.net/2Strings

#Text.Combine
#CrossJoin
#PartialText

For a list of my Excel courses at Lynda/LinkedIn:
https://www.linkedin.com/learning/instructors/oz-du-soleil
There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2