Power Query Table.AddIndexColumn only for non-null elements

[gtaranti]

Hi all,

I have a problem with the followiing table :

Column1                  |  OTHER COLUMNS ....    |  
--------------------------------------------------|
QuestionX                |   other values ....    |
null                     |   other values ....    |
null                     |   other values ....    |
QuestionY                |   other values ...     |
null                     |   other values ....    |
null                     |   other values ....    |
null                     |   other values ....    |
QuestionZ                |   other values ...     |
...
..
..

I'm trying to add an Index Column but taking into account only the non-null elements of Column1.

i.e. the new column should have 1 in QuestionX, 2 in QuestionY, 3 in QuestionZ.

(QuestionX, QuestionY, etc are all arbitrary text. and the number of intermediate nulls is also random)

My ultimate goal is to rename the Questions in Column1 to Question1, Question2 e.t.c

Is there a  way to do that?

[v-chuncz-msft]

@gtaranti,

You may follow the steps below.

1) add an index column in Query Editor

2) add a calculated column in DAX

Column =
IF (
    NOT ( ISBLANK ( Table1[Column1] ) ),
    RANKX (
        FILTER ( Table1, NOT ( ISBLANK ( Table1[Column1] ) ) ),
        Table1[Index],
        ,
        ASC,
        DENSE
    )
)

[gtaranti · Accepted solution]

Thanks, but I'm not using PowerBI (maybe I used the wrong forum to post). 

I'm using only Excel with Power Query, trying to clean some data (exported responses from SurveyMonkey online forms)

So I don't know where to enter the DAX expression.

But finally, I came up with a solution with Power Query.

I'm building as a mid-step a translation table on-the-fly, and using it to get the final column like that :

    ...
    InitialQuestionsList = List.RemoveNulls(Table.Column(Source, "Column1")),
    QuestionsListNumbers = List.Numbers(1, List.Count(InitialQuestionsList)),
    QuestionsList = List.Transform(QuestionsListNumbers, each "Q" & Text.From(_)),
    TranslationTable = Table.FromColumns({QuestionsListNumbers,InitialQuestionsList}),
    Final = List.Accumulate(Table.ToRows(TranslationTable), Source, (t,r) => Table.ReplaceValue(t, r{1}, r{0}, Replacer.ReplaceValue, Table.ColumnNames(t)))

I don't know if it the best/easiest solution, but it works.

[v-chuncz-msft]

@gtaranti,

Glad to hear that. You may help accept solution. Your contribution is highly appreciated.