Forum Discussion
How to compare two columns and return data from a third column?
Hi Community,
Example data set in Power Query
| Hours | Project Catagory | Production Catagory | Internal (formula) | External (formula) |
| 3 | Men's | Men's | 3 | null |
| 5 | Women's | Women's | 5 | null |
| 6 | Kid's | Kid's | 6 | null |
| 2 | Men's | Family | null | 2 |
| 4 | Family | Men's | null | 4 |
| 1 | Women's | Women's | 1 | null |
What I need to do
To compare the values between the "Project Category" and the "Production Category".
If it's a match, get the value from the "Hours" column and put it in the "Internal" column. If not, put the hours into the "External" column.
The question
Is there a Power Query formula or function that can do this? Maybe I could work out the "Internal" column and apply the same method to the "External" column?
Many thanks!
Hi, Anonymous ;
You could add conditional column as follow:
Then add another conditional column.
The final show:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlbSUfJNzYspNTAwMi9G5cTqRCuZAoXC83ORVaBwQWrMgILemSkIFUgckLwRuh1uibmZOZVgORMEF8NuQ0J2xwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Hours = _t, #"Project Catagory" = _t, #"Production Catagory" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Hours", Int64.Type}, {"Project Catagory", type text}, {"Production Catagory", type text}}), #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Internal", each if [Project Catagory] = [Production Catagory] then [Hours] else null), #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "External", each if [Project Catagory] <> [Production Catagory] then [Hours] else null) in #"Added Conditional Column1"
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
It's fairly straightforward. Add a custom column using :
if then else
You just use the names of the columns to compare the 2 columns required and put the desired result and an alternative
--
So you'll need to 2 custom columns
Hi, Anonymous ;
You could add conditional column as follow:
Then add another conditional column.
The final show:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlbSUfJNzYspNTAwMi9G5cTqRCuZAoXC83ORVaBwQWrMgILemSkIFUgckLwRuh1uibmZOZVgORMEF8NuQ0J2xwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Hours = _t, #"Project Catagory" = _t, #"Production Catagory" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Hours", Int64.Type}, {"Project Catagory", type text}, {"Production Catagory", type text}}), #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Internal", each if [Project Catagory] = [Production Catagory] then [Hours] else null), #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "External", each if [Project Catagory] <> [Production Catagory] then [Hours] else null) in #"Added Conditional Column1"
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
