Forum Discussion
Conditionally Replace Negative and 0 Values with values from another table
Hi I would like to ask if I am able to replace negative and 0 values from another table using Power Query.
I Have 2 Tables, Option A and Option B. Both Tables have 4 main columns: Category, Value 1, Value 2, and Value 3.
I would like for every Negative or 0 values in Value 1, Value 2 and Value 3 of Option A to be replaced with the values of Option B.
If the value in Option B does not exist for that category, or if the replacement value in Option B is still negative/Zero, then the value of Option A will still be used and hence not replaced.
I Would like to achieve this via Power Query as means of data preparation. I am however, open to any other solutions that may achieve the same goal.
Here is a sample file with Option A, Option B and an Expected Output Table based on the rule given above: https://docs.google.com/spreadsheets/d/1QV7yaT87h5BTzILr7eafoPBWy-NKCRm9/edit?usp=sharing&ouid=113561460355971571582&rtpof=true&sd=true
Any help is appreciated, thank you!
patrickrw99 , I think you have first merge these tables and then create conditional columns
After merging in Option A, you will two-column for each
Value 1 new
= if [Value 1] <=0 [Option 2.Value 1] else [Value 1]
you can create column like these and delete other columns at the end
1 Reply
- amitchandak
Super User
patrickrw99 , I think you have first merge these tables and then create conditional columns
After merging in Option A, you will two-column for each
Value 1 new
= if [Value 1] <=0 [Option 2.Value 1] else [Value 1]
you can create column like these and delete other columns at the end