cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Eliminate the rows with rows of a column having null values

Hi All,

I have dataset with application id as unique.

The dataset consists of a column percenatge for applicationid=10  with values "null" and "98" so for applicationid=10 the dataset has 2 rows

and other application id =11 with only "null"  so for applicationid=11 the dataset has 1 row.

So that i cant filter out null .

I need to eliminate the null values if the applicationid has other value.

1 ACCEPTED SOLUTION
Community Support

Hi @pabburi

I test with another method and it seems to get results as expected.

create index column in query editor and  created columns in table1

`isblank = IF(ISBLANK([Percentage]),1,0)`
`count1 = CALCULATE(COUNT([Application ID]),ALLEXCEPT(Table1,Table1[Application ID]))`
`count2 = CALCULATE(COUNT([Application ID]),FILTER(ALLEXCEPT(Table1,Table1[Application ID]),[isblank]=1))`
`flag = IF([count1]=[count2],1,BLANK())`
```Column =
VAR dip =
CALCULATE (
COUNT ( Table1[Application ID] ),
FILTER ( ALLEXCEPT ( Table1, Table1[Application ID] ), [flag] = 1 )
)
VAR minvalue =
CALCULATE ( MIN ( [Index] ), ALLEXCEPT ( Table1, Table1[Application ID] ) )
RETURN
IF ( dip > 1 && [Index] = minvalue, 1, 0 )```

then create a new table

```Table new =
SUMMARIZE (
FILTER (
Table1,
NOT ( ISBLANK ( [Percentage] ) )
|| ISBLANK ( [Percentage] )
&& [count1] = 1
|| [Column] = 1
),
[Application ID],
[Percentage],
[value]
)```

Best Regards

Maggie

5 REPLIES 5
Community Support

Hi @pabburi

I test with another method and it seems to get results as expected.

create index column in query editor and  created columns in table1

`isblank = IF(ISBLANK([Percentage]),1,0)`
`count1 = CALCULATE(COUNT([Application ID]),ALLEXCEPT(Table1,Table1[Application ID]))`
`count2 = CALCULATE(COUNT([Application ID]),FILTER(ALLEXCEPT(Table1,Table1[Application ID]),[isblank]=1))`
`flag = IF([count1]=[count2],1,BLANK())`
```Column =
VAR dip =
CALCULATE (
COUNT ( Table1[Application ID] ),
FILTER ( ALLEXCEPT ( Table1, Table1[Application ID] ), [flag] = 1 )
)
VAR minvalue =
CALCULATE ( MIN ( [Index] ), ALLEXCEPT ( Table1, Table1[Application ID] ) )
RETURN
IF ( dip > 1 && [Index] = minvalue, 1, 0 )```

then create a new table

```Table new =
SUMMARIZE (
FILTER (
Table1,
NOT ( ISBLANK ( [Percentage] ) )
|| ISBLANK ( [Percentage] )
&& [count1] = 1
|| [Column] = 1
),
[Application ID],
[Percentage],
[value]
)```

Best Regards

Maggie

Community Champion

Hi @pabburi

Try this calculated table. Assuming your TableName is Table1

From the modelling tab>>NEW TABLE

```New Table =
VAR temp =
Table1,
"RowsCount", CALCULATE (
COUNTROWS ( DISTINCT ( Table1 ) ),
ALLEXCEPT ( Table1, Table1[Application ID] )
)
)
VAR FilteredTable =
FILTER (
temp,
OR (
NOT ( ISBLANK ( [Percentage] ) )
&& [RowsCount] > 1,
ISBLANK ( [Percentage] )
&& [RowsCount] = 1
)
)
RETURN
SELECTCOLUMNS (
FilteredTable,
"Application ID", [Application ID],
"Percentage", [Percentage]
)```

Regards
Zubair

Frequent Visitor

Thanks Zubair for the help,but i have a small query.Will this code work if there are other column as well along with applicationid and percentage.

And in the sample attachment of yours in new table why application id 6 is repeated twice and application id 5 is not present(i.e App ID 5 is present in Table1).

Community Support

Hi @pabburi

After testing with  Zubair's solution, I can explain more for you.

"Will this code work if there are other column as well along with applicationid and percentage."

Yes, you can add columns in the last query "SELECTCOLUMNS"

"why application id 6 is repeated twice and application id 5 is not present(i.e App ID 5 is present in Table1)"

When modifying the condition in the "filter" expression, you can get App ID 5 to present.

```New Table =VAR temp =    ADDCOLUMNS (        Table1,        "RowsCount", CALCULATE (            COUNTROWS ( DISTINCT ( Table1 ) ),            ALLEXCEPT ( Table1, Table1[Application ID] )        )    )VAR FilteredTable =    FILTER (        temp,        OR (            NOT ( ISBLANK ( [Percentage] ) ),            ISBLANK ( [Percentage] )                && [RowsCount] = 1        )    )RETURN    SELECTCOLUMNS (        FilteredTable,        "Application ID", [Application ID],        "Percentage", [Percentage],        "value", [value]    )
```

Best regards

Maggie

Community Champion

@pabburi

Check the attached sample file as well

Regards
Zubair

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors