Power Query: Import from PDF (2 Examples)

/in /by

One thing has been torture for those of us who work with data: breaking data out of PDF files!

Just recently, Power Query got a new feature: import from PDF. In this video I show 2 examples of importing a PDF into Power Query and also show the old methods of a plain old copy-paste and “open with Microsoft Word.”

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

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

Managing Accuracy and Errant Entries with SUMIFS and Conditional Formatting

/in /by

This video pulls together a number of Excel features in order to get an accurate inventory number.

This is based on a real life situation where an organizations data was messy because they weren’t adding numbers properly or managing errant entries.

This video shows how to use SUMIFS, a dropdown list and Excel’s Conditional Formatting to ensure that inputs and outputs are accurate.

#SUMIFS
#ExcelErrors
#ConditionalFormatting

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

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

Excel COUNTIFS function with OR Criteria

/in /by

#COUNTIFS
#CountMultipleCriteria
#COUNTIFSfunction

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

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

Power Query: Split Multiple Columns into Rows All At Once

/in /by

It’s easy to split columns in Power Query and split into rows. But here’s a situation where there’s data in 3 columns that will fly apart and get unmatched if we split one column without splitting the other 2 at the same time.

One solution would be to split the source data into 3 pieces, split the columns into rows, add index columns, then use outer joins to piece the data back together.

We’re not going to do that today!We’re going to use Power Query’s lists, and 2 functions:
Text.Split
Table.FromColumns

These are going to make for a smooth solution!

#SplitMultipleColumns
#PowerQuery
#Text.Split

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

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

Power Query: Combine rows into a single cell with Text.Combine

/in /by

A question came in from someone who wanted to know how to merge rows of data into a single cell.

Imagine you have a person’s name in one column and then details about the person on multiple rows; and then another name and details about that person; and so on …

Now you’d like to merge each person’s details so that each person and their respective details are on a single row with their details in a single cell/column.

To do this, I show Power Query and the Text.Combine function.

This video also demonstrates my example of building a small model so that we can focus on the solution and not a massive amount of our real data.

#Text.Combine

#PowerQuery
#Grouping

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

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

Excel’s LET function in 3 Acts

/in /by

One of Excel’s newest functions: LET()
Incredible! This function allows the Excel user to create complex formulas that are easier to create and easier to read. But they do require the user to think several steps ahead.

In this video I show 3 examples and make 2 cases in favor of using LET:
– Very basic
– Recycle. This is when we have to do a complex calculation and then do it again later in the formula.
– Clarity. Here, when a complex formula is needed, it’s easier to set up the variables first and then do the final calculation using only the variables. This can be easier to real in several distinct chunks rather than a massive jungle of parentheses or use of helper columns.

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

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

Try … Otherwise: Power Query’s version of Excel’s IFERROR

/in /by

Thank to my friend Gráinne who showed me this in Power Query:

Try … Otherwise

In native Excel we have the IFERROR function for when we anticipate errors and know what we want to do with them. In this case, we want to add the Shipped and Inventory amounts. But, if someone has put a note in the Inventory column–instead of an error–we want to return the Shipped amount. Pretty easy to do with Try … Otherwise

There’s also a warning: Power Query does not treat 0, null and [blank] as the same things.

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

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

Excel Discussion: The hard way or the concise way?

/in /by

This video is a little different than the rest. Someone needed help and I came up with 2 solutions–both shown in this video. What’s more important about this is the discussion about the hard solution vs. the concise solution.

Sometimes the hard solution serves us better because we understand it. The concise solution may look impressive, but the question is: can we explain it and be responsible for it?

The solution is for someone who needed to move parts of a string of text. She needed to “move the D.” The solutions include:

– Column from Example
– Split column by delimiter
– Merge columns
– Handling leading zeroes in Power Query
– Data Types

#LeadingZeroes
#PowerQuery
#ColumnFromExample

  For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

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

Power Query: Calculating Incentive Points using Advanced Group-by & Left Outer Join

/in /by

A friend contacted me and asked how Excel might help calculate points for what he described as a “buddy system” where he goes to the gym. Here’s the challenge:

– Marvin and Drew are paired in this buddy system
– If one attends a class, the team gets 1 point.
– If both attend together, the team gets 4 points.
— How can we easily tally each team’s points?

This video looks at 3 teams (6 people) and a list of classes. In order to solve this, we use Power Query’s: group-by, advanced group-by, load to connection only, and a left outer join.

Download the workbook: https://datascopic.net/buddies

#PowerQuery
#OuterJoin
#GroupBy

 For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

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