Forum Discussion
Set two columns based on another column
Rjesak , hi try with this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WilTSUfIICAKRmcUFiXmZyboBpalFJfkKQZnJiXk4xWN1oHqRVCAzYfJQBOIC6WDHECDpWJyZmKcbkpGYCeMoxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Hisp" = _t, #"National Origin Code" = _t, #"National Origin" = _t, #"National Origin Group" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Hisp", type text}, {"National Origin Code", type text}, {"National Origin", type text}, {"National Origin Group", type text}}), #"ReplacewithConditionsNationalOriginCode"= Table.ReplaceValue(#"Changed Type",each [National Origin Code], each if [Hisp] ="Y" and [National Origin Code]="" then "H" else [National Origin Code],Replacer.ReplaceValue,{"National Origin Code"}), #"ReplacewithConditionsNationalOrigin"= Table.ReplaceValue(#"ReplacewithConditionsNationalOriginCode",each [National Origin], each if [Hisp] ="Y" and [National Origin]="" then "Hispanic" else [National Origin],Replacer.ReplaceValue,{"National Origin"}), #"ReplacewithConditionsNationalOriginGroup"= Table.ReplaceValue(#"ReplacewithConditionsNationalOrigin",each [National Origin Group], each if [Hisp] ="Y" and [National Origin Group]="" then "Hispanic" else [National Origin Group],Replacer.ReplaceValue,{"National Origin Group"}) in #"ReplacewithConditionsNationalOriginGroup"Also you can create conditional columns and delete the originals.
Regards
Victor
Hi Rjesak
Based on my understanding, it doesn't support for any column in power query to change based on other columns.
If you table is as below, could you complete the example data to give an example of your expected result?
| Hispanic? | National Origin Code | National Origin |
Best Regards
Maggie
Here's how the table looks:
Hispanic? | National Origin Code | National Origin | National Origin Group |
| Y | HPR | Hispanic-Puerto Rican | Hispanic-Puerto Rican |
| Y | H | Hispanic | Hispanic |
| Y | |||
| SAT | Asian-Thai | Asian |
This is how I want it to look:
Hispanic? | National Origin Code | National Origin | National Origin Group |
| Y | HPR | Hispanic-Puerto Rican | Hispanic-Puerto Rican |
| Y | H | Hispanic | Hispanic |
| Y | H | Hispanic | Hispanic |
| SAT | Asian-Thai | Asian |
All I've been able to come up with is to create a conditional column which is = to National_Origin_Code if that column is populated or if it is not populated and Hispanic? is Y, then set it to H. Then build conditional columns for the other two (National Origin and National Origin Group) based on the new code column.
Rjesak , hi try with this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WilTSUfIICAKRmcUFiXmZyboBpalFJfkKQZnJiXk4xWN1oHqRVCAzYfJQBOIC6WDHECDpWJyZmKcbkpGYCeMoxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Hisp" = _t, #"National Origin Code" = _t, #"National Origin" = _t, #"National Origin Group" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Hisp", type text}, {"National Origin Code", type text}, {"National Origin", type text}, {"National Origin Group", type text}}), #"ReplacewithConditionsNationalOriginCode"= Table.ReplaceValue(#"Changed Type",each [National Origin Code], each if [Hisp] ="Y" and [National Origin Code]="" then "H" else [National Origin Code],Replacer.ReplaceValue,{"National Origin Code"}), #"ReplacewithConditionsNationalOrigin"= Table.ReplaceValue(#"ReplacewithConditionsNationalOriginCode",each [National Origin], each if [Hisp] ="Y" and [National Origin]="" then "Hispanic" else [National Origin],Replacer.ReplaceValue,{"National Origin"}), #"ReplacewithConditionsNationalOriginGroup"= Table.ReplaceValue(#"ReplacewithConditionsNationalOrigin",each [National Origin Group], each if [Hisp] ="Y" and [National Origin Group]="" then "Hispanic" else [National Origin Group],Replacer.ReplaceValue,{"National Origin Group"}) in #"ReplacewithConditionsNationalOriginGroup"Also you can create conditional columns and delete the originals.
Regards
Victor