Assigning values in one column based on values in two other columns

[chrisqqq123]

Hi all, ...I have been using Power Query for creating enormous Excel tables from multiple files in a folder, and getting better at manipulating the data. I have run across a problem I have not been able to solve. I have a column with customer names (it also includes other data like numbers), and I need to apply those customer names to numerous cells based on other columns. For example, "Ed" is the customer for "town." How can I apply the customer "Ed" to all the rows that are labeled"town"? (There are hundreds of "Name" values) The second table in the picture is the table I'd like to get to, and I'm trying to use Advanced Editor in Power Query to get this result. I will eventually keep Column B, as I need to retain the number values.

So how can I add the customer names to the appropriate values for the "Name" column?

Thank you!

[MFelix · Accepted solution]

Hi @chrisqqq123,

Create a new custom column with the following code:

if [ColumnB] = "customer" then [Customer] else null

Then just sort by Name and by customer name.

Finally just make the fill down and you will get all the values you need.

See below the code I used:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKskvz1PSUQKi9KLUvDylWB24WHJpcUl+bmoRkOmagixhAtIA4idnllQC2UbmFkAyrzQnB1kUSXtwYi6yDEx7cUliSSqQY2gOJMxMUcSQdZckJleiSIINiAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, ColumnB = _t, Customer = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"ColumnB", type text}, {"Customer", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Customer Name", each if [ColumnB] = "customer" then [Customer] else null),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Name", Order.Ascending}, {"Customer Name", Order.Descending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows",{"Customer Name"})
in
    #"Filled Down"

Be carefull that this is assuming that your customer is always the same for the column name if you have more than one customer name for city you can have an issue since the name may not stay correct.

Regards,

MFelix