Power Query: Sorry, Not on the List
7 March 2018
Welcome to our Power Query blog. This week, I take a look at how extracting data from multiple lists can be confusing when nulls are involved.
I have a table of lists, which I need to extract. I am hoping that I will be able to keep the data aligned so that it makes sense. In real life, this would be more likely to be long references, but my chosen data will make the problem I am about to encounter more obvious.
I want to extract the values from my list, so I click on the little split line icon at the right of the ‘Column1’ heading.
If I were to choose the ‘Expand to New Rows’ option, I would get a row for each value in each of my lists. I don’t want this; I want to see my lists one above the other. I choose ‘Extract Values’:
I can choose a delimiter to separate the values on my list, so I choose to use a semicolon.
My data starts off well. “A FOR APPLE” goes together properly, as does “E FOR EATEN”. However, I don’t think “P IS FOR QUERY” is quite so good. If I check the data, I see that things start to go wrong at “I”, which is definitely not for “JOKER”!
If I go back a step and look at my data in rows using the ‘Expand to New Rows’ option instead, I can check why the data doesn’t make sense after “I”.
In the entry where the word beginning with ‘I’ should have gone, there is a null. The number of entries is right, and I can see my data is in the list, so why has Power Query ignored the null entry and misaligned my data?
To see the answer, I need to go back to my ‘Extract Values’ step and look at the M language behind it.
= Table.TransformColumns(#"Converted to Table",
{"Column1", each Text.Combine(List.Transform(_, Text.From), ";"), type text})
The culprit here is Text.Combine. I can test this with a simple scenario in another blank query.
The M language I use here is very simple, and it is easy to see what happens.
= Text.Combine({"one", null, "two"})
This combines the text in the list provided and ignores the null. Therefore, my data will be misaligned. How I get around this would be to replace the nulls in my lists with something else – and that something else really depends on the context. It could be ‘N/A’, spaces or whatever makes sense. In my alphabet example, I will replace my null with “-----“ until I can think of a good word for “I”.
It would be great if Power Query could use the GUI replace option for this:
However, since the values are not currently visible, this will not work. I need some List functionality.
List.ReplaceValue(list as list, oldValue as any, newValue as any, replacer as function) as list
where list is the list to modify, oldValue is the value to replace (the null in my case), and newValue is the value to replace it with (“-----“). The argument replacer is used to tell Power Query what kind of values are being replaced, and in this case, I would use Replacer.ReplaceValue.
I need to add this step before I start manipulating the lists. In practice, it would be best to apply this to any list where there is a risk of nulls being present, but I will add this to my second list, as that is the one causing me problems.
Since my lists are defined in my source, the M language I need for the second list is:
= {List_Letter,List.ReplaceValue(List_Word,null,"-----",Replacer.ReplaceValue)}
Now when I execute the query again, I see my lists aligned correctly:
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!
