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

(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

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

Just 2 days ago it became possible to import Dynamic Arrays into Power Query. 🙏🏼🎉
This is really cool. HOWEVER! There are 8 warnings that I take you through.

First. There is no longer “table-slash-range.” The icon has been changed to “From Sheet.” BOOOO! 🤪 Table/range seems more accurate.

Check out the video for the other 7 warnings. They’re too hard to explain. You just have to see them.

#DynamicArrays
#ImportFromDynamicArrays
#Power Query

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