Power Query: Obstructed View

Welcome to our Power Query blog. This week, I look at some errors that can be encountered using Table.View().

In Power Query: Flat with a View, I looked at how Table.View() can be used to make loading queries from flat files (data that has not come from a relational database) more efficient. Having experienced the errors that can occur, this is a rough guide to what can go wrong, what that looks like, and how to fix it. The example I will be using is the Flat out query I used in the original blog.

The Table.View() step has been applied correctly here, and the query looks fine.

There are three main errors you can make with Table.View(), and Power Query’s powers of detection diminish as we go!

The first error is impossible to miss:

In fairness, Power Query does issue an error for this in the Advanced Editor, but if I click 'Done' without checking, I could assume that I’d lost all my steps! In this case, it is a common error: I missed a comma at the end of the ‘Sorted Rows1’ step.

Clicking on ‘Show error’ highlights the next step after the comma was expected, so I correct it by adding a comma. This tells Power Query another step is coming; only the last step has no comma at the end.

Once I add the comma, I have the green tick, and I can click ‘Done’ to see that my steps are still there.

The next error is more subtle.

Note the red wavy line. This means that there is something wrong with the last step. I have made a deliberate mistake, which is the most common one I have seen when applying a Table.View() step.

In this case, the name of the column on the previous step was Amount, but in the Table.View() step I have called it amount (lower case ‘a’). The Advanced Editor will not detect this as an error, and the column will appear with the name amount on the final step. If the final step has a red wavy line, check the column names and data types in the Table.View() step against the PREVIOUS step.

The final error is not picked up by Power Query at all, but you will soon notice when you try to use the data!

There’s no red wavy line and everything looks fine. That is, until you look at the contents of the Amount column, for example. They are not numbers! The numbers are in the Description column. This is what happens when the columns are in the wrong order in the Table.View() statement, compared with the previous step.

The order of the columns MUST be the same as the previous step. This applies to the GetType() and OnTake() parameters. You will get no warning for this error: you must simply check.

Incidentally, if the line under the column heading is partially grey, it is an indication of how many nulls are in that column and this is not a problem.

When using Table.View() to help with loading times into the Power BI model, look out for these issues, otherwise you will end up creating more problems than you solve!

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