Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
pabburi
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
v-juanli-msft
Community Support
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 )

12.png

then create a new table

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

13.png

 

Best Regards

Maggie

View solution in original post

5 REPLIES 5
v-juanli-msft
Community Support
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 )

12.png

then create a new table

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

13.png

 

Best Regards

Maggie

Zubair_Muhammad
Community Champion
Community Champion

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

 

 


Regards
Zubair

Please try my custom visuals

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

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

15.png

 

Best regards

Maggie

@pabburi

 

Check the attached sample file as well

 

 


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.