Add missing values to power query result set based on criteria

Question

I have a final result set from 5 Workbook Queries in Excel. One of the columns in this query has empty cells since during a query merge there were no matching values to fill.

Now I'd like to create a multiple IF function that will read the adjacent columns values and try to figure out the right value to fill in the blank ones.

OEM No      Manufacturer
3855860141  Mercedes-Benz
310807      Sachs
6704210112  
9062411113  
0009970653  
2213201738  
9063262681  
3954100622  
6113240350  
6113240450  

So for example in the Manufacturer column if we had the first blank cell to be b4 I'd like to fill the values with a function such as

IF( len(A4) = 10 , "Mercedes",
IF( len(A4) = 11 , "MAN",
IF(LEFT(A4,2) = "81"), "Behr"))

Two things I'd like to do with this.

1. Fill the columns and recursively update the tables where the queries derived from.

2. Keep the changes.

Answer 1

I would first add a column for the Length of the OEM No. In the Power Query Editor window, select the column then from the Add Column ribbon, choose Extract / Length.

Then I would use the Conditional Column feature (also on the Add Column ribbon) to add a series of tests and results. In that pop-up you select from choices to generate code. I would set it up something like:

if Length = 10 then Mercedes

else if Length = 11 then MAN

else if OEM No starts with 81 then Behr

Power Query writes to new tables - its better not to try to overwrite the same table.