Set two columns based on another column

[Rjesak]

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 used to group national origins based on the first one or three characters - begins with H for Hispanic/Latino NO group)

National Origin (text string with the spelled out description of the full National Origin Code - for instance "Puerto Rican National Origin"

These are all columns in the database.

I created a computed column called National Origin Group based on the codes in National Origin Code.

The problem is that we have a number of situations where the Hispanic box is checked but no specific national origin is selected. What I'd like to do is write a Power Query statement that says something like this:

if ["Hispanic?"] = "Y" and ["National Origin Code"] is null
then National Origin Code = "H" and National Origin = "Hispanic"
end

This way my group column will include all the rows where Hispanic was checked but no national origin was selected. As you can see from the above, I'm not very familiar with M query yet. 🙂

[v-juanli-msft]

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

[Rjesak]

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.

[Vvelarde · Accepted solution]

@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