Daniel Choi solved it using Power Query! Let’s review his solutions.

Download Daniel’s Power Query solutions.

Recap

It’s the same messy data & requirement found in my last post.

Daniel’s PQ Solutions

He has two Power Query solutions. #1 requires writing some M code. #2 only uses the UI.

I prefer #1! Why?

• M code is shorter
• easier to read the M in #1
• Great way to learn about List !

PQ Solution #1

Steps Source, Change Type, Replace Value are easy steps.

Text_Split

Step Text_Split is a great practical use of creating a List!

I could read the entire M code but best to digest this single step in the formula bar (listed below).

Text_Split = Table.AddColumn(#”Replaced Value”, “TextSplit”, each Text.Split([Time 1],”,”)),

Add new column “TextSplit” based on previous step. For each comma in column [Time 1] split it into separate pieces. Where does PQ put these separate pieces of split text? Into a List!

In column “TextSplit” click in the white space to the right of any “List” to see the list values.

time_intervals

This step is longer but I understood it after a couple of minutes!

Add column called time_intervals based on step Text_Split. And now the heart of this step:

each List.Transform([TextSplit],each Number.From(Text.AfterDelimiter(_,”-“)) – Number.From(Text.BeforeDelimiter(_,”-“)))

List.Transform: reference a list and do something with it’s items. For each item (eg “9-10”) Text.AfterDelimiter gets the “10”, Text.BeforeDelimiter gets the “9”. Number.From converts both text to a number. 10 – 9 = 1.

Now each list contains numbers.

Total_time_intervals

List.Sum sums numbers in each list.

Total_time_intervals = Table.AddColumn(time_intervals, “Total time_intervals”, each List.Sum([time_intervals])),

The final two steps remove columns and change a data type.

PQ Solution #2

I decided not to review this but I included it (top). Why? Solution #1 demonstrates that venturing away from the UI (user interface) and using a bit of M can simplify/shorten the M code. It’s exciting to learn something new especially during these difficult times. Solution #2 isn’t bad…it’s interesting but a longer approach for those that prefer to stick with the UI. I need to push forward with learning about List in M.

Lists: Learn More!

I have so much to learn about list functions but I’ll get there one example at a time. Thanks to Daniel for sharing his solutions with me. I’m still learning to think in the Power Query manner. It has taken time but I’m getting there. Sometimes it takes someone like Daniel to nudge me in the right direction.

About Me

My name is Kevin Lehrbass. I’m a Data Analyst. I live in Markham Ontario Canada. The last few years seem like a renaissance in Excel with Microsoft adding so many new features: dynamic arrays, datatypes, new functions like XLOOKUP etc. Power Query has been around for several years now but I’m still learning it.