Forum Discussion
How to prevent null values from loading into data model when importing data from cube with MDX query
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!
Did you try removing Empty rows from Power Query (keeping your original MDX)?
8 Replies
You could simply exclude the nulls from the MDX itself:
non empty
{
(
[Promotion].[Promotion Code Name].children - [Promotion].[Promotion Code Name].&
)
} on rowsYou can also do this in Power Query filter. But since you already have an MDX code, it is better to do direclty in MDX.
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!
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.