My issue is when I refresh in Desktop, all my data loads, but I get an error per image 1 below and when I go into query editor, the error table is empty as per image 2 below. To reconcile prior to submitting question I've done the following:
1. Confirmed final step in query runs without issue. No errors in query editor. I have reconciled any errors throughout the applied steps to ensure the final query result is clean. No column has errors.
2. Searched all columns for NaN or Infinity, there are none (which has been an issue in the past).
3. Ensured all data types match between excel file and in Power Query. For example, all "text" columns in excel are entered as "text" in query editor. Same with "percents", "numbers", "dates".
4. Aligned all of the query and excel data type with data types in desktop.
5. Reordered columns so that excel order and query order are aligned.
6. Read as many postings as possible to try and find a solution.
The data seems to load fine and dashboards and reports online are functional but I'm concerned I cannot get rid of the error message. Please help. Note I'm not an advanced user (though I'm learning my way around) so please be detailed with explanation. This is a similar issue to a recent post but am unable to resolve. @Jimmy801 and @dax replied on prior issue but am now stuck again.
Hello @wahib_mouhoubi ,
I did ultimately solve the issue, though, it required cell by cell review of my data set to align anything that did not match the data type. Ultimately, it was a data conversion issue as @edhans mentions.
I did try turning on Column Quality and Column Distribution, as well as filtering for errors, but that did not help (though great features!).
One action I find useful is publishing online to help identify the error. After I publish, I'll refresh the dataset and if there's an error it will stop and show the "!" issue. The detail of what happened often provides more insight than what shows up in desktop. Though, it doesn't always help.
Again, ultimately it took a detailed review to align all cells with column type. After you review your data, if it doesn't work, let me know. Happy to keep troubleshooting. This was awhile back so it may help me to remember any additional details.
Thank you for the replies.
@dax-Image 2 in original post is what appears when I click on the "view errors" in image 1. My challenge is the error table is empty and I can't figure out why.
@edhans- when viewing all 425 records, there are no errors reported, including after turning on Column Distribution and Quality. All columns show 0% error, varying amounts of Valid and Empty across columns, but no errors.
Continuing to try and resolve I replaced all BLANK cell values with NULL in Power Query and that reduced from 423 errors to 379, step in the right direction. I then replaced all "- None - " values with BLANK in my base excel file, then saved and updated in Power Query, then in Desktop. To clarify, everything loads fine in Power Query, but when I refresh in Desktop I still get the 379 erros.
Still 379 errors, with empty error query, all columns showing 0% error.
I appreciate additional ideas and support.
Can you replicate your query in Excel's Power Query, then dump the results to a Table in a spreadsheet? That will let you look at it in detail.
Some things that might help diagnonsis this:
1. What is your data source type(SQL, Excel, Cosmos DB, ect...)
2. Do you make use of any "Within the last X days" filter?
3. Once your data is loaded, try setting the value type of non string columns to string. It is possible that a column was set to number even though it usually isn't
It is only 423 records, so you should be able to see it visibly. Go up a step, above "Kept Errors" and you should see all 425 records. Scan through those in all columns looking for ERROR.
I suspect it is a data conversion error - trying to convert alpha chars to a number.
Also, in your original table, turn on Column Distribution and Column Quality on the View tab of Power Query. Then refresh everything and go to that specific query and look for red error bars in the fields.
Hi @ROKRI ,
I don't know the detailed error, so if possible, could you please inform me more detailed error information (You could click "View error" in your first image to check the error information)?
Thanks for your understanding and support.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Check out new user group experience and if you are a leader please create your group!
On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks
Check out how to claim yours today!
Test your skills now with the Cloud Skills Challenge.