Fill empty cells (similar to index - match)

jordanbuchan359

New Member
Joined
Jun 15, 2018
Messages
15
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:

NameCertificationIdentification
GemmaEnglish1234
GemmaPE1234
GemmaScience1234
GemmaArt1234
Drama1234
History1234
French1234

<tbody>
</tbody>

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

Code:
[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
HTML:
https://community.powerbi.com/t5/Desktop/Fill-empty-cells-based-on-criteria/td-p/101190

Any help would be appreciated.
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

VBA Geek

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
This kind of transformation is better handled within Power Query than in DAX ...
 

Watch MrExcel Video

Forum statistics

Threads
1,108,655
Messages
5,524,131
Members
409,561
Latest member
ay123

This Week's Hot Topics

Top