none
How to read arbitrary data from Excel workbooks RRS feed

  • Question

  • I used Power Query/From File/From Excel to read data from an Excel workbook -- and received "Error" in some of the result cells. On inspection, I discovered that the source workbook contains "#N/A" in those cells.  I fixed this problem by changing these cells to "#(#)N/A" -- that is, by using the M language literal escape sequence.

    My complaint: I think I should be able to read any data from a source such as Excel.  Requiring me to go back and fix the source data in order to make it readable by Power Query is not very reasonable. In this case, that's especially true because the source file comes from another group so I don't control the data contents. And this example is just one of many such workbooks that I have to deal with, from many different groups.

    Should I look for another automated solution to cleanup my incoming data before I submit it to Power Query?  Cannot anyone recommend a tool for that?

    Wednesday, September 9, 2015 1:06 PM

Answers

  • I was confronted with the same difficulty. I opted to replace all errors to null values. The following M code performs the replacement on all columns at the same time. It assumes that the preceding step is named "RawData".

    = Table.ReplaceErrorValues( RawData, List.Transform(Table.ColumnNames(RawData), each {_,null}))

    Wednesday, September 9, 2015 4:44 PM

All replies

  • I don't have another tool to recommend because what you require can probably be done in Power Query.

    #N/A represents an error condition in an Excel cell. Power Query represents all of Excel cell error types as simply "error." How would you like Power Query to handle the error values, given that it has no influence on the source?

    Wednesday, September 9, 2015 2:09 PM
  • I was confronted with the same difficulty. I opted to replace all errors to null values. The following M code performs the replacement on all columns at the same time. It assumes that the preceding step is named "RawData".

    = Table.ReplaceErrorValues( RawData, List.Transform(Table.ColumnNames(RawData), each {_,null}))

    Wednesday, September 9, 2015 4:44 PM