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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Certification[/TD]
[TD]Identification[/TD]
[/TR]
[TR]
[TD]Gemma[/TD]
[TD]English[/TD]
[TD]1234[/TD]
[/TR]
[TR]
[TD]Gemma[/TD]
[TD]PE[/TD]
[TD]1234[/TD]
[/TR]
[TR]
[TD]Gemma[/TD]
[TD]Science[/TD]
[TD]1234[/TD]
[/TR]
[TR]
[TD]Gemma[/TD]
[TD]Art[/TD]
[TD]1234[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Drama[/TD]
[TD]1234[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]History[/TD]
[TD]1234[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]French[/TD]
[TD]1234[/TD]
[/TR]
</tbody>[/TABLE]

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