Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi there,
In Power Query Editor, is there a way to isolate the faulty cell responsible for the Invalid cell value '#REF!' error? I have removed errors & nulls and replaced values Error ->Null. I have also confirmed that there are no formulas/errors in the source sheets. The error is occuring when transposing a table from Rows to Columns.
Solved! Go to Solution.
Hi @jpellegrini
Are you sure there are no errors in your source sheet? Transposing a table won't create a #REF error. Such an error will be there in the source data.
Try this:
1. Duplicate your query
2. Delete all steps except the Source & Navigation steps
3. Close & Apply - this should result in a pop-up like this telling you there are errors in your query.
4. Click on the View errors link in the yellow-highlighted section.
5. This creates a new query in the PQ Editor that lists the errors in your query, including the row number where the error is
6. Check the rows for your error(s).
If that doesn't help you find the issue please supply your source data file.
Regards
Phil
Proud to be a Super User!
Thanks Phil. Saved me a lot of effort with this solution. 🙂
Thanks very much @PhilipTreacy . Indeed, as you allude to, it was an error in the source sheet. I was overlooking several #Value! errors and fixed this with a Replace -> 0 command. You suggest a good way of isolating the errors in question. Thanks again for your response.
Hi @jpellegrini
Are you sure there are no errors in your source sheet? Transposing a table won't create a #REF error. Such an error will be there in the source data.
Try this:
1. Duplicate your query
2. Delete all steps except the Source & Navigation steps
3. Close & Apply - this should result in a pop-up like this telling you there are errors in your query.
4. Click on the View errors link in the yellow-highlighted section.
5. This creates a new query in the PQ Editor that lists the errors in your query, including the row number where the error is
6. Check the rows for your error(s).
If that doesn't help you find the issue please supply your source data file.
Regards
Phil
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |