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 […]
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 […]
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 […]
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 […]
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 […]