null date from SSAS cube shows default date 12/30/1899

[lprince]

I have a MS SSAS cube that's the source of my report, so I can't modify the data in any way.  I have a table that displays a mix of dim and fact data on the report/dashboard.  Where the dates are null, it is displaying 12/30/1899.  Is there any way I can get it to just display blank?  I am not using the fields with the potential for null dates in a slicer or on a chart, just in the table.  

thanks!

[dkay84_PowerBI]

You can still use the query editor to transform your data, so I would try replacing the values there.  It sounds like the "nulls" are not registering as actual nulls, as that date corresponds to a numeric value of 0 being converted to a date data type.  You could also try adding a column with DAX that checks if the value is 0 return BLANK() otherwise give the date.

[lprince]

My query editor menu is greyed out except for Data Source Settings, but that just gives me the servername and Database Name.  My data source is a SQL Server Analysis Services database using an Enterprise gateway; I can view the data in cube or through SSMS and the data in the field is null.  It seems something that Power BI interface is doing, maybe to make it slicer friendly?  

If there's a way to edit the data, I would be ok with that because that would help a different problem I have with the report.

[dkay84_PowerBI]

I'm not sure why you can't access the query editor.  Can you right click on the fields list and add a column?

[lprince]

nope,  thus my frustration.  I only get collapse all, expand all.  I have Power BI Desktop, version Version: 2.42.4611.701 64-bit (January, 2017).  I have used other data sources and been able to edit data with no problem.  Seems specific to SSAS Cube source.  I built my data warehouse cube, so I have any and all permissions.  Should I turn it in as a bug or feature request?

[dkay84_PowerBI]

Are you connecting using import or direct query?

[lprince]

Left is a snippet showing the options when I pick Get Data, Analysis services.  I fill in the server name, the database name and Connect Live is selected by default.  I don't want to import and while I could write a MDX or a DAX; that will make me have to re-write/modify the query if I add fields to the Dim tables. I didn't want to do that as I'm still working on the structure and if I let others build reports, I want them to be able to pick any available fields.  

The data is NULL, Power BI just isn't interpreting it that way.  I did google a similar problem in SSRS, where SSRS was being helpful by displaying a default date for SSAS null data, but the way they solved it was to format the field with an iif statement, which can't be done here either.   I'll end up writing an SSRS report and putting the link on the dashboard, it's just not pretty and not mobile friendly. The SSAS server is 2012 BI, so maybe it's something between Power BI and that. 

[dkay84_PowerBI · Accepted solution]

It may have something to do with the compatibility level.  Power BI is essentially a local instance of SSAS tabular with compatibility level 1200.  Not sure where you would go from here, but without the option to use the query editor, DAX or MDX, I don't know what to tell you.  I would open a support ticket if possible

[lprince]

I appreciate you working through it with me; I was hoping I missed some setting.  I'm about to move to SQL 2016 so I can add the query your data feature, so maybe that will fix it also.  I hate nulls, but for this dataset they're unavoidable.  

[dkay84_PowerBI]

You could also try passing in an MDX?DAX query on connection to apply the transformation