Hello,
I am trying to upivot a table made of 24columns and 8250rows.
The table is a result of 22 excel files containing 15 sheets each of them.
Using power query I have clean them all and I have created a table of 24columns and 8250rows. Each column represent a month.
I want now unpivot the table to be able to work out MTD and YTD figures.
The result table is quite big in terms of row and when I try to load it in power pivot/and or in excel I get a nasty error message “Data.Format invalid cell value “#DIV/0!” so the loading fails.
As the table is quite big, I am not able to locate which column is generating the error. I have tried a number of things
I changed all the column type to text (the only exception to this is the column containing value).
a) I tried with the “removes error” functionality
b) I tried with “replacing” error functionality (I change the "error" cells to a letter “z”)
c) I tried the same things on the value column removing and / or replacing the "error" cells with value 0
d) I have also tried with the article /link below (well I was asked to remove the link)
Another option could be to try to locate the formula generating the error on the source excel sheet / file.
I would like to avoid that as it might be very time consuming.
Cheers,
Diego
I am trying to upivot a table made of 24columns and 8250rows.
The table is a result of 22 excel files containing 15 sheets each of them.
Using power query I have clean them all and I have created a table of 24columns and 8250rows. Each column represent a month.
I want now unpivot the table to be able to work out MTD and YTD figures.
The result table is quite big in terms of row and when I try to load it in power pivot/and or in excel I get a nasty error message “Data.Format invalid cell value “#DIV/0!” so the loading fails.
As the table is quite big, I am not able to locate which column is generating the error. I have tried a number of things
I changed all the column type to text (the only exception to this is the column containing value).
a) I tried with the “removes error” functionality
b) I tried with “replacing” error functionality (I change the "error" cells to a letter “z”)
c) I tried the same things on the value column removing and / or replacing the "error" cells with value 0
d) I have also tried with the article /link below (well I was asked to remove the link)
Another option could be to try to locate the formula generating the error on the source excel sheet / file.
I would like to avoid that as it might be very time consuming.
Cheers,
Diego