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

0
votes

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.

1
powerbipowerquerym
Could you please provide the code and ideally a sample file? – Aleksei Zhigulin
@AlekseiZhigulin: I updated my question. Unfortunately I can't share source data and it seems issue is related to that. I hope my update helps to identify the issue – Máté Juhász
Thanks, the entire query's code could be useful. I can only suggest adding an index column, determining which row the error occurs in, then checking this row in the data source for possible anomalies. – Aleksei Zhigulin

1 Answers

0
votes

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