I have a list of shirt colors and the suggested sizes of them. I would like to create a new column in the query and have a value of list based on the filtered elements ( I have no idea how to explain it differently, feel free to correct me).
So the rules are:
- If the current color has XS in the occurrence list then the value of the row needs to be "YES"
- If the current color does not have XS but has XXL,XL or L then the value should be "XYES"
- Otherwise the value needs to be "NO"
| Jacket Color | Jacket Size |
|---|---|
| Black | XS |
| Black | XS |
| Black | S |
| Blue | XS |
| Blue | L |
| Blue | XL |
| Blue | XXL |
| Blue | XL |
| Blue | XXL |
| Green | XS |
| Green | S |
| Green | M |
| Red | XS |
| Red | XXL |
| Red | S |
| Red | XXL |
| White | S |
| White | M |
The table should look like this:
| Jacket Color | Jacket Size | New_col |
|---|---|---|
| Black | XS | YES |
| Black | XS | YES |
| Black | S | YES |
| Blue | XS | XYES |
| Blue | L | XYES |
| Blue | XL | XYES |
| Blue | XXL | XYES |
| Blue | XL | XYES |
| Blue | XXL | XYES |
| Green | XS | YES |
| Green | S | YES |
| Green | M | YES |
| Red | XS | XYES |
| Red | XXL | XYES |
| Red | S | XYES |
| Red | XXL | XYES |
| White | S | NO |
| White | M | NO |
I am not that big of a tech guy myself, if you can help me how to google the answer, that is good aswell.
Thank you in advance.
Tried everything I could with this little knowledge I have about power query. If this could be solved by me, I would have a job right now.
CodePudding user response：
Your result data doesn't match your sample data but this is the process.
- Import data in PQ
- Select Jacket Color and then group by from the ribbon. Enter the following:
Add a new custom column from the ribbon and enter the following:
if List.Contains([All][Jacket Size], "XS") then "Yes" else if List.ContainsAny([All][Jacket Size], {"XXL","XL", "L"}) then "XYES" else "No"
- Expand the column to get all rows back.
