Power Query: Fruit Frustration – Part 1

Welcome to our Power Query blog. This week, I look at how changing steps can lose data.

This week, I am loading some juicy data into a Query. I have specified an Excel Workbook as the source:

The M code I have used is:

(where the file path is personal to me).  I want to extract some data out of the Apples table, so I filter on ‘Name’:

This gives me one row. I only want the Data column, so I select this, and right click to ‘Remove Other Columns’.

I can then expand the data:

I choose all the columns and I don’t need to ‘Use original column name as prefix’:

Well, that’s all done, I have my apples, so I’ll see you next week. Wait! I changed my mind: I want to squeeze some data out of my Oranges table. I need to go back to the ‘Filtered Rows’ step.

If I click on the cog icon for this step, I can change it.

I change the dropdown to ‘Oranges’ and click ‘OK’.

This all looks fine, so I look at the ‘Expanded Data’:

I have oranges. Well, this is all working nicely, I’ll just check out the bananas next.

I’ve changed ‘Filtered Rows’ to select Bananas, so now I can check the ‘Expanded Data’:

This is not how to spell banana. Where has the middle column gone? If I click on the gear icon next to ‘Expanded Data’, I can see what has happened:

One of these columns does not actually exist for Bananas. The second column is called two, not 2. If I select two and deselect 2, the data looks correct:

If I go back to the expand options, there is no longer an option to select 2.

So, what happened? A little knowledge is a dangerous thing! Power Query will try to adapt if steps are changed, added or deleted. Since nothing triggered an error, it appears that everything is okay until the user notices the missing column. This can be difficult to spot. This is the Oranges table in the source file:

Although the table looked fine, there was a missing column. Next time I’ll look at what went wrong.

I’ll leave with one final challenge – I haven’t changed anything in the query, but now I have a fetching red line under the columns? Why? The answer is in this blog!

Come back next time for more ways to use Power Query!