Power Query usually does a really good job selecting the data type automatically for us. However, when it misses on a mixed column, I need to force it back to the intended data type. That's easy for most data columns, but when you want that elusive "ABC, 123" (Any) data type, i do not see a way to revert it back.
Here is a column that I used Excel to calculate the time it took us to close a "Hold" (time to fix a non-conformance of some kind). Some columns will calculate to a value (as the hold is closed) while others will calculate to "Open" as the hold is still open.
I suppose I could calculate to "Today's Date" or even allow it to force the numbers to text and re-classify them with isnumber later, but I'd rather keep my formula as is, since i further use this column to feed a dashboard in several different ways.
As of now, when I hand off the sheet to Power BI, it sees the text as an error, and stops the data from loading to my Data Warehouse.
So, any takers?? How do we get PQ to classify this as "Any"?
[Image Can Not Be Found][Image Can Not Be Found]
The image you intended to upload doesn't show, but I guess that you added the duration column by selecting the Hold and Start columns and then 'Subtract days' under the 'Date' icon in the user interface. Then you probably added som code to return "Open" in case Hold is empty.
You probably ended up with M-code looking like this:
