Search code examples
powerbipowerquerym

strange DataFormat.Error: We couldn't convert to Number. Details: nan

DataFormat.Error: We couldn't convert to Number
Details: nan

enter image description here

I keep getting above error and I just can't get it solved.

The same error message appears both when:

  • I try to perform Table.ExpandTableColumn
  • try to filter only rows with errors
    • same error whether I specify column(s) in table.selectrowswitherrors or not
    • I don't expect this table to contain errors, however that case it should just return empty table (and it indeed does for other tables)

I don't have any division in my data model, so it's really strange how nan could distributed (it's the result of 0/0 in Power Query)

update

It seems I've some corrupted rows in my source data, by filtering down my table, there is a row with "Error" at the bottom:
enter image description here

Unfortunately I can't see it's details as clicking on one of the "Error"s gives error message:
enter image description here

Also when I try to remove errors, that row is still not removed:
enter image description here

The source data is in Excel (200k+ rows), I removed all empty rows below the used range in case there would be an extra row used there which cause the issue, but it didn't help.

Solution

  • Finally I could solve the problem by adding RemoveRowsWithErrors much earlier in the code, when the error was present only in one column and not propagated to the whole row.

    As its suggested also here: https://app.powerbi.com/groups/me/apps/3605fd5a-4c2e-46aa-bee9-1e413fc6028a/reports/dd7a5d70-dca1-44c5-a8f4-7af5961fe429/ReportSection