Filling in the blanks...

[Anonymous]

I have heard I can use an IF with ISBLANK to fill in blanks is a table and supply more info if I supply a more detailed use case.

So here it is:

a table with blanks and values without blanks that contain the key named groupid

name  groupid

blank           10

microsoft  10

microsoft  10

blank           10

blank           10

amazon      12

blank           12

the end result:

microsoft   10

microsoft  10

microsoft  10

microsoft  10

microsoft  10

amazon      12

amazon      12

Thanks!

[Icey · Accepted solution]

Hi @Anonymous ,

Please check:

Column =
VAR LastNonBlankDate =
    CALCULATE (
        LASTNONBLANK ( 'Table'[groupid], 1 ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[groupid] = EARLIER ( 'Table'[groupid] )
                && NOT ( ISBLANK ( 'Table'[name] ) )
        )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[name] ),
        FILTER ( ALL ( 'Table' ), 'Table'[groupid] = LastNonBlankDate )
    )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Ok I added an IF THEN for the use case of where the groupid was 0. I put it in front of the CALCULATE.

Great job!!

[Anonymous]

This was well done and I almost hate to ask but how would you modify it put in blank as a default value for the groupid say 0?

Many Thanks!

[Anonymous]

Hi Icey,

I tried putting this in the dialog box for creating a custom column and I am getting an Eof error as shown below.

[Anonymous]

Ok. Seems Power Query will not work with this but new Column will. I did not realize this limitation. I do not get this error when I use this.

Many Thanks now back to testing this.

[FrankAT]

Hi @Anonymous ,

you can do this with Power Query:

1. Select the column with Blanks.
2. Tab Transform > Fill > Fill Down | Fill Up.

Regrads FrankAT

[Anonymous]

I saw that earlier but I gave a simple use case. I amm wondering how does that work if you do not specify a key that is shared like I gave but a real table may have the key in one column of say 40. So how does the fill handle that?

Thanks,

-Tony

[Greg_Deckler]

Not sure I get what you are saying here. You want to fill a column that has blanks but that what you want to fill with comes from a different column?

[Anonymous]

Ok I think this will get me what I want but getting eof error on the first bracket. My thought process is get the group id for the current record then use that id get the last non blank record name that has the same group id which gets put into the new column at the same position as the record that has the gid value.

=

VAR gid = [GROUPID]
RETURN
LASTNONBLANK([ORGANIZATIONNAME],GROUPID = gid))

[Anonymous]

Correct. When there is a blank I want to fill it or create a new column with data value for that same row with the blank column but it has a value that came from another row with the same key but a non blank value.

I hope that helps

[Anonymous]

So in my example there were two rows with blanks with the groupid of 10 and so what I would do is take another row with groupid of ten that has a nonblank name in the case 'microsoft' and fill it in or as you suggested create a new column and place the name there for the same row that has a groupid of 10 and the blank name.

Thanks!