How to fill all rows of a column based on a value form a row in another column [Power Query Editor]

[Mariamawit96]

Hello, 

I am trying to create a custom column to return the names of all companies that have responded "False" and transform the names into ex. anonymous company if they have responded "True" to the consent question  BUT also to the rest of the questions, using Power Query Editor (i cannot use DAX). i have a table with one column containing all the  questions and one column containing all the answers  and a column with the company name. 

I have tried using the IF function to first create a column "consent" with the responses True & False:

each if [question_ID] = ".question66" then [Response] else ""

then i created another column "Label" to lable them with the name of the company if they responded False and as "anonymous company" if they responded True:

each if [consent]="True" then "anonymous company" else if [consent]="False" then [companyName] else "" )

However, even if the formula works i have the issue of having blank row for the other questions, instead of having them filled out 

question_ID	Response	Company Name	Consent	Label
.question66	True	Apple	True	anonymous company
.question77	xx	Apple
.question5	xx	Apple

whereas what i would like is the following:

question_ID	Response	Company Name	Consent	Label
.question66	True	Apple	True	anonymous company
.question77	xx	Apple	True	anonymous company
.question5	xx	Apple	True	anonymous company

can anyone help? Thank you.

[jgeddes · Accepted solution]

Ok, lets try adding an index column for each Company Name using Group By (select All Rows as the Operation)

With the resulting table of company names add an index column starting at 1

you should end up with something like

Expand the tables in the count column, deselect Company Name and Use Original Column Name as Prefix

Now we can add a column

Now we can fill down to populate the label column

And we should end up with

Hope this gets you going in the right direction.

[Mariamawit96]

Thank you very much for your help, i have applied all the steps that you have outlined however, the formula returns all blank values:

the formula i used is (translated in english) :

= Table.AddColumn(#"Expanded Count", "NEW_label", each if [questionID] = ".question66" and Text.Upper([response]) = "True" then "anonymous company" else if [questionID] = ".question66" and Text.Upper([response])="False" then [company name] else null)

how can i solve it? thank you again.

[jgeddes]

I wrote Text.Upper into the formula as I was not sure if your response values were typed in by hand or if they were computer generated. It helps capture all values that could be typed. But in order for it to work you have to make sure to follow the Text.Upper statement with a value that is in upper case. So in this instance make sure you have Text.Upper([response]) = "TRUE" (as opposed to "True") You will have to edit the false part to FALSE as well.

[Mariamawit96]

okay thank you.