Forum Discussion
Solved
Set two columns based on another column
Our data has three columns I'm referencing here (it's poorly designed but it's what I've got): Hispanic? (Yes or null based on a checkbox) National Origin Code (four character code which can be use...
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
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