How to prevent null values from loading into data model when importing data from cube with MDX query

[Anonymous]

How can I remove the null row from my MDX query results OR prevent null values from loading into my data model? 

I'd prefer to prevent these records from loading rather than filtering out null values because the null values make my data source much larger than it needs to be. 

Here is the query I'm currently working with

 select
    non empty 
    {
[Measures].[Average Trips Per Day]
,[Measures].[Calories Burned]
,[Measures].[Carbon Offset]
,[Measures].[Median Distance]
,[Measures].[Median Duration]
,[Measures].[Rider Trips]
,[Measures].[Rides Per Bike Per Day]
,[Measures].[Total Distance]
,[Measures].[Total Riders]
,[Measures].[Total Trip Duration in Minutes]
,[Measures].[Total Members]
    } on columns
    ,
    non empty 
    {
    (
    [Promotion].[Promotion Code Name].children
)
    } on rows 
from [BCycle]
where ([Program].[Program Name].&[Madison B-cycle])

When I run this query, I still get a row for Promotion Code Name = NULL; I want to exclude that row but cannot figure out how. Any advice or help you can provide would be much appreciated!  

[AkhilAshok]

You could simply exclude the nulls from the MDX itself:

non empty 
 {
 (
 [Promotion].[Promotion Code Name].children - [Promotion].[Promotion Code Name].&
)
 } on rows 

You can also do this in Power Query filter. But since you already have an MDX code, it is better to do direclty in MDX.

[Anonymous]

Thanks for the suggestion. I tried adding that to my query & running in SSMS. I get a memory error very quickly & I'm thinking its because my data source is so large?

Do you have any other suggestions? 

Is there a way I can remove the rows with a power query filter? I'd prefer to "hard delete" these rows, not hide them. 

Many thanks!

[Anonymous]

I tried adding that to my MDX query in power bi & I got this error. Would you be able to assist?

[AkhilAshok · Accepted solution]

Did you try removing Empty rows from Power Query (keeping your original MDX)?

[Anonymous]

Yepp, that worked! So that means as my data refreshes, it will pull in everything & then filter it to non-null values?

[v-danhe-msft]

Hi @Anonymous,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

Regards,

Daniel He

[Anonymous]

Yes, thank you for your insight. It is much appreciated. 

[AkhilAshok]

You can just click on the drop down besides the Product Code Name column in Power Query editor and press Remove Empty. You can consider this as 'hard delete' since null vlaues won't be loaded to your data model.