Fill a column based on a value in two other columns

[gbrmdc]

Hello, everyone!

 

I'm working with a form that is automatically downloaded from a server and it consists of a table with responses from multiple different forms. Each row of the table is a different question/response, and each response has an ID associated with it. So, the columns basically tells me: Response ID Number; What kind of form it is; What's being asked; The response of the question.

 

One of the questions is a dropdown list of 24 locations to choose from (not editable by the user). In each row that contains the answer to this question, I can find the location and the ID associated with the response and, therefore, I can know where the response is coming from.

 

I've created a conditional column that is filled when it finds one of these 24 locations on the response column (otherwise it says "verify"), but that only fills the row with the location question. I need to have all of the lines from the same response ID filled with the location name. Something like, having PowerBI find the value of "response column" if "response ID" = X and if "response column" value is one of the 24 locations, and then populating the rows in a new column with the value of "response column" if "response ID" = X

 

PS: in portuguese, which is the language of the table, verify = verificar and location = localidade/local

 

Can anyone help me? Thanks in advance!

[gbrmdc]

Hello, everyone!

I'm working with a form that is automatically downloaded from a server and it consists of a table with responses from multiple different forms. Each row of the table is a different question/response, and each response has an ID associated with it. So, the columns basically tells me: Response ID Number; What kind of form it is; What's being asked; The response of the question.

One of the questions is a dropdown list of 24 locations to choose from (not editable by the user). In each row that contains the answer to this question, I can find the location and the ID associated with the response and, therefore, I can know where the response is coming from.

I've created a conditional column that is filled when it finds one of these 24 locations on the response column (otherwise it says "verify"), but that only fills the row with the location question. I need to have all of the lines from the same response ID filled with the location name. Something like, having PowerBI find the value of "response column" if "response ID" = X and if "response column" value is one of the 24 locations, and then populating the rows in a new column with the value of "response column" if "response ID" = X

PS: in portuguese, which is the language of the table, verify = verificar and location = localidade/local

Can anyone help me? Thanks in advance!

[v-shex-msft]

HI @gbrmdc,

If you mean fill column with answers column value and replace blank value as 'verify'. you can try to use below formula:

#"Added Custom" = Table.AddColumn(#"preview step", "custom", each if [Column1.answer] <> "" then [Column1.answer] else "verify")

>>which is the language of the table, verify = verificar and location = localidade/local

You can modify regional setting in options or choose advanced download option to choose a particular language version of power bi desktop.

Regards,

Xiaoxin Sheng

[gbrmdc]

Hello @v-shex-msft!

Thanks for your reply 🙂

What I meant was create a new column and have the location answer (in the picture I sent, they are "Mutuca" and "Pico") replicated to the other lines with same Response ID number

Is that possible?

[PattemManohar]

"Mutuca" and "Pico" has different Response ID as per the screenshot. 

[gbrmdc]

Hi @PattemManohar,

Yes, because they're associated with different form responses. What I need is to have the other lines with the same Response ID as "Mutuca" to say "Mutuca" instead of "Verify" in the Location or a new personalized column, the lines with same Response ID as "Pico" to say "Pico" and so on

[PattemManohar]

Could you please post your expected output based on the Input screenshot or any sample input & output data will also be fine..

[gbrmdc]

It should look like this after adjustments:

[PattemManohar]

Could you please try the following...

Add a "New Column" using below expression...

Check = CALCULATE(MAX('LookupValue'[Answer]),FILTER(ALL('LookupValue'),'LookupValue'[Question]="Location" && 'LookupValue'[ResponseID] = EARLIER([ResponseID])))

InputOutput

[gbrmdc]

Dear @PattemManohar,

Thanks for your help!

I am getting a "token literal expected" error when I try to add this formula:

If I click on "show error", it points to the first apostrophe on 'LookupValue'

Regards,

Gabriel

[PattemManohar · Accepted solution]

You need to add the column using that expression in "Data" pane using "New Column" NOT in "Power Query Editor".

Also please note in the below expression LookupValue is name of the table I've stored sample data, in your case it should be your tablename. As LookupValue is a DAX keyword as well, that's why it was mentioned in single quote (I might have used another name to avoid confusion)

Check = CALCULATE(MAX('LookupValue'[Answer]),FILTER(ALL('LookupValue'),'LookupValue'[Question]="Location" && 'LookupValue'[ResponseID] = EARLIER([ResponseID])))

[mhennes1]

Hello!

I am having an issue with this expression in my application. I am looking to do essentially the same thing as OP, I have a report with survey data and each row is a different question. The individual surveys are identified by column "Survey Invitation ID" -- I would like to create a new column that fills with the answer to one of the questions based off of the survey ID (i.e. if customer fills out Survey ID 1 and indicates that they work at a university, the new column would identify each row labeled as Survey ID 1 as being a university customer). However, when I tried to use the formula provided in this post I am getting some false results. Attached is a screenshot of what the Survey ID column looks like, as well as the results of the new formula. The second Survey ID with 3 associated rows does not have a response value, but the formula is populating results regardless.

[gbrmdc]

@PattemManohar,

What I'm getting now is this error saying it can't locate the table 'LookupValue':

[PattemManohar]

As mentioned above, use your table name in place of "LookupValue" (It is the table name I've created for testing your scenario)

[gbrmdc]

Oh gotcha!!

Thank you so much for your help @PattemManohar!

It worked perfectly 🙂

Regards,
Gabriel

[PattemManohar]

You are welcome !! Gabriel