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. […]
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 […]
(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 […]
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 […]
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 […]
If you missed the 2021 Global Excel Summit, full event content now available via the event website π https://globalexcelsummit.com/ This video is my presentation: Risk Taking as a Content Creator #GlobalExcelSummit #ContentCreation #RiskTaking 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 […]
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 […]
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 […]
In a previous video I dealt with a common need to split multiple columns of data. However, in that dataset the delimiter was obvious and all cells were filled in. But! What happens when things get weird? In this video, the delimiter is a line-feed and there’s a problem with empty cells. We use Text.Split […]