Power Query: Fruit Frustration – Part 2

Welcome to our Power Query blog. This week, I explain the problems when changing steps.

Last week I asked a question. I had created a query to extract data from an Excel Workbook.

I had then compared the data extracted to the source:

When I looked again at my query, although I hadn’t changed it, I had a fetching red line under the columns? Why?

The answer becomes apparent if I refresh the data:

I can’t have the source file open while I am accessing it in Power Query. Even more annoying, I can’t close the source Excel Workbook while I have my query open! This is linked to the rule that if I have Power Query open, I can’t do anything in Excel anywhere on the same computer.

Once I have closed the source, the error may persist, in which case I must refresh the data again.

Having resolved that issue, I am left with the problem I had when I changed a step in my query. I changed the ‘Filtered Rows’ step to pick Oranges instead of Apples:

The data in my ‘Expanded Data’ step was missing an orange.

Oh my darling, no clementine.  The reason is that Power Query is great at changing steps, but it has some weaknesses.  One of these is column names.  If I take a closer look at the ‘Expanded Data’ step:

This is telling me that Columns from the Data table in the table “Remove Other Columns” are being extracted.  The column names in the Data table are 1,2 and 3, and these columns are being extracted to columns 1, 2 and 3 in the query.  However, if I look at the gear icon next to ‘Expanded Data’

I can see that there is a Column 4, which I may select.

The M code for this step is now:

This now extracts Column 4 successfully.  Therefore, although it may be tempting to change steps to save time, it’s important to check the results, especially where column names are concerned. 

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