Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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. 🙂
Solved! Go to 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
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
User | Count |
---|---|
23 | |
18 | |
16 | |
13 | |
11 |