Fill empty cells (similar to index - match)

[jordanbuchan359]

Hi,

I'm hoping for a solution to my predicament. I have an external data sheet linked via power query - the table contains names, certifications and identification numbers. My problem is that some of the students names are missing; however, their corresponding ID number has populated in every row... If possible, I'd like to use their ID number to fill in any blanks.

I tried using fill down, but for some reason it wouldn't fill all of the blanks.

Here's an example table:

Name	Certification	Identification
Gemma	English	1234
Gemma	PE	1234
Gemma	Science	1234
Gemma	Art	1234
	Drama	1234
	History	1234
	French	1234

<tbody>
</tbody>

I tried the following code for a custom column, but was prompted with a Token EOF error for my VAR declaration:

[COLOR=#000000][FONT=Consolas]VAR Identification = [Identification][/FONT][/COLOR]RETURN
    CALCULATE (
        FIRSTNONBLANK ( [Name], 1 ),
        FILTER (
            ALL,
            [Identification] = Identification
                && [Name] <> BLANK ()
                && [Name] <> ""
        ) [COLOR=#000000][FONT=Consolas]    )[/FONT][/COLOR]

Note - the above code was adapted from the following thread

https://community.powerbi.com/t5/Desktop/Fill-empty-cells-based-on-criteria/td-p/101190

Any help would be appreciated.

 

[VBA Geek]

This kind of transformation is better handled within Power Query than in DAX ...