Eliminate the rows with rows of a column having null values

[pabburi]

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.

[v-juanli-msft · Accepted solution]

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

[Zubair_Muhammad]

Hi @pabburi

Try this calculated table. Assuming your TableName is Table1

From the modelling tab>>NEW TABLE

New Table =
VAR temp =
    ADDCOLUMNS (
        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]
    )

[pabburi]

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).

[v-juanli-msft]

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

[Zubair_Muhammad]

@pabburi

Check the attached sample file as well