cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chrisqqq123 New Member
New Member

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

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.

 

table1.jpg

 

 

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

Thank you!

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

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.

 

filll.gif

 

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




1 REPLY 1
Super User
Super User

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

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.

 

filll.gif

 

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 416 members 4,297 guests
Please welcome our newest community members: