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.
I have a Excel data source with some data errors. In some colums a calculation failure occurs resulting in #Num! in Excel.
The data source is being unpivoted in PowerQuery. All records containing #Num! are being ingnored in the unpivoting proces resulting in dataloss.
- Skipping the rows is not an option. I need the rest of the data in the records
- Replace #Num! replacing by eg. "0" seems not to work
Not loading the columns containing the #Num! is an option, but I could not find any way to do this.
Any help?
Change it to Text before you unpivot.
Unfortunately changing the data type to text, any or whatever does not work
I suppose the column is of a numeric type. Try to change it to Text befor you unpivot the table, and afterward turn the column back to numeric.
Unfortunately not: Data type is "any"
Have you tried to replace #NUM! by null ?
Tried the following solutions without any luck
#"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type", {{"Column70", 0}}),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type", {{"Column70", null}}),
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.