Power Query: Zipping Through a List

Welcome to our Power Query blog.  This week, I look at the M function List.Zip and its uses.

I have been looking at List functions in M language recently, and this one reminds me of my problems with null values in lists (please see Power Query: Sorry, Not on the List).

The description of the function in the Microsoft help pages is straightforward:

Takes a list of lists, and returns a list of lists combining items at the same position.

Since I have a simple example with two lists below, this will be my starting point.  I also want to see if the function will cope with a null.

I create a new blank query from the ‘New Query’ option on the ‘Get & Transform’ section of the ‘Data’ tab by choosing ‘Blank Query’ from the dropdown in the ‘From Other Sources’ section.

The syntax I have used is: 

The functionality has created a list of lists.  To extract the values, I can convert to a table using the ‘To Table’ option in the ‘Convert’ section on the ‘List Tools / Transform’ tab.

I now have a column which I can extract the values from, using the split arrows icon.

I will choose to use a space as a delimiter between my values.

My values are extracted, and this time the null value appears against the ‘I FOR’ instead of offsetting the values. If the lists were of different lengths, then null would also be used in place of any ‘missing’ values at the end.

If I wanted to check what was in each of my ‘list’ values in the original list without going through the table conversion, I can simply click on the list I want to see.

My example has two lists, but I can enter multiple lists, as shown in the example below:

This time I have used a list of three simple lists, viz. 

Since my third list is two entries instead of three, a null is inserted at the end of the last list.

Want to read more about Power Query? A complete list of all our Power Query blogs can be found here. Come back next time for more ways to use Power Query!