Forum Discussion
Fill a column based on a value in two other columns
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!
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])))
15 Replies
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!
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
Hello Anonymous!
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
Community Champion
"Mutuca" and "Pico" has different Response ID as per the screenshot.
