Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to 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.
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 ) )
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.
Glad to hear that. You may help accept solution. Your contribution is highly appreciated.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
102 | |
84 | |
79 | |
68 |
User | Count |
---|---|
120 | |
110 | |
94 | |
81 | |
77 |