Combine text from multiple cells into one cell if name matches

[maggiec27]

I want to combine the text for any cell in column C if the name in column A matches. Right now I'm using a simple IF/Text Join formula. This won't work for me. =IF(A4=A3,TEXTJOIN(";",TRUE,B3:B5)) It's too simplistic.

I'd really like to look at all of column A and if there are any matches/duplicates, then text join all values in column C for the first match of all matches. If we can flag the other matches so I can quickly delete them or if there is a macro that could do all of this and delete the subsequent matches that would be great. Is this possible?

Name	Product	Products Combined
Maggie Smith	Duffel Bag	Duffel Bag; Protein Shake; Protein Powder
Karen Jones	Vitamins
Joann Bright	Protein Bar
Maggie Smith	Protein Shake	Flag for deletion or automatically delete
Maggie Smith	Protein Powder	Flag for deletion or automatically delete

<tbody>
</tbody>

 

[sandy666]

use
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Returned Quantity", each Table.Column([Count],"Returned Quantity")),
instead
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Returned Quantity", each List.Distinct(Table.Column([Count],"Returned Quantity"))),

you can remove List.Distinct from all custom columns and you will get all values - duplicated or not duplicated

 

[maggiec27]

What can I use in Power Query if the file I receive has SKU numbers for the product name instead of the name? Currently, I was doing an index/match formula manually to find the product name for the sku. I inserted a second tab with the sku numbers and product names and did the index match to that. Then I ran the power query which extracted the product name values. It'd be cool if I could automate that process though and not have to manually add the product name column, fill the formula, then run power query.

I tried updating my power query by clicking add custom column and was thinking I could write a formula in there, but not sure how to use index match and reference my other sheet in the formula box. Maybe there is a different way of doing it in power query?


File with data

Customer Name	SKU	Helper column for Index/Match formula
Maggie	1001	=index(match.......) If SKU = 1001, value here is Duffel Bag

<tbody>
</tbody>

Master Product/SKU Data (Separate file adding in as an extra tab currently but open to something different)

1001	Duffel Bag
1002	Vitamins
1003	Protein Shake

<tbody>
</tbody>

 

[sandy666]

Forget about vba or formulas with Poweruery. Change way of thinking.

ad rem:
you've two table
Tb1: customer and sku
Tb2: sku and product

first you need connect tb2 to tb1 by sku then you can do what you want with the result, eg. post#7
with you example from post#12
add table1 to PQ
add table2 to PQ
merge these tables by SKU

[SIZE=1]let
    Source = Table.NestedJoin(Table1,{"SKU"},Table2,{"SKU"},"Table2",JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Product"}, {"Product"})
in
    #"Expanded Table2"[/SIZE]

Table1			Table2			Result
Customer Name	SKU		SKU	Product		Customer Name	SKU	Product
Maggie	1001​		1001​	Duffel Bag		Maggie	1001​	Duffel Bag
			1002​	Vitamins
			1003​	Protein Shake

or I missed something?

edit:
add more representative examples with five or more rows

 

[maggiec27]

This is my query but I'm getting a cyclic reference error: The query worked until I tried editing the source.

let
Source = Table.NestedJoin(Table1,{"SKU"},Table2,{"SKU"},"Table2",JoinKind.LeftOuter),
#"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Product"}, {"Product"}),
#"Grouped Rows" = Table.Group(Source, {"Customer Name"}, {{"Count", each _, type table}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Product", each Table.Column([Count],"Product")),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Order Number", each Table.Column([Count],"Order Number")),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Returned Quantity", each Table.Column([Count],"Quantity Returned")),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Shipping Address", each List.Distinct(Table.Column([Count],"Address1"))),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "Shipping Primary Reason", each List.Distinct(Table.Column([Count],"Reason For Return"))),
#"Added Custom5" = Table.AddColumn(#"Added Custom4", "Shipping Zipcode", each List.Distinct(Table.Column([Count],"Zip"))),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom5",{"Count"}),
#"Extracted Values" = Table.TransformColumns(#"Removed Columns", {"Product", each Text.Combine(List.Transform(_, Text.From), "; "), type text}),
#"Extracted Values1" = Table.TransformColumns(#"Extracted Values", {"Order Number", each Text.Combine(List.Transform(_, Text.From), "; "), type text}),
#"Extracted Values2" = Table.TransformColumns(#"Extracted Values1", {"Returned Quantity", each Text.Combine(List.Transform(_, Text.From), "; "), type text}),
#"Extracted Values3" = Table.TransformColumns(#"Extracted Values2", {"Shipping Address", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
#"Extracted Values4" = Table.TransformColumns(#"Extracted Values3", {"Shipping Primary Reason", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
#"Extracted Values5" = Table.TransformColumns(#"Extracted Values4", {"Shipping Zipcode", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
#"Added Custom7" = Table.AddColumn(#"Extracted Values5", "Owner ID", each "00G40000002JXdb"),
#"Added Custom6" = Table.AddColumn(#"Added Custom7", "Case Origin", each "Returns Log"),
#"Added Custom8" = Table.AddColumn(#"Added Custom6", "Record Type ID", each "01240000000Ue13")
in
#"Added Custom8"

Table 1: Customer and Return Details

Order Number	Customer Name	Address1	City	State	Zip	Reason For Return	Quantity Returned	SKU
99323462	Maggie Curry	1234 Happy Street	Dallas	TX	75212	RTS - Return To Sender	1	1001
99323462	Maggie Curry	1235 Happy Street	Dallas	TX	75212	RTS - Return To Sender	2	1002
99323463	Karen Jones	1236 Jones Road	Addison	Tx	75213	RTS - Return To Sender	3	1003
99323464	Ben Smith	1237 Willow Way	Plano	TX	75214	RTS - Return To Sender	1	105
99323465	Maggie Curry	1238 Happy Street	Dallas	TX	75212	RTS - Return To Sender	1	1006

<tbody>
</tbody>

Table 2:

SKU	Product
1001	Duffel Bags
1002	Protein Shake
1003	Vitamins
1005	Blender Bottle
1006	T-Shirt

<tbody>
</tbody>

 

[sandy666]

Because you didn't show expected result here is merged and grouped by Customer Name result:
(use CODE tags if you posting any code)

[SIZE=1]let
    Source = Table.NestedJoin(Table1,{"SKU"},Table2,{"SKU"},"Table2",JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Product"}, {"Product"}),
    #"Grouped Rows" = Table.Group(#"Expanded Table2", {"Customer Name"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Order Number", each Table.Column([Count],"Order Number")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Order Number", each Text.Combine(List.Transform(_, Text.From), "; "), type text}),
    #"Added Custom1" = Table.AddColumn(#"Extracted Values", "Address1", each Table.Column([Count],"Address1")),
    #"Extracted Values1" = Table.TransformColumns(#"Added Custom1", {"Address1", each Text.Combine(List.Transform(_, Text.From), "; "), type text}),
    #"Added Custom2" = Table.AddColumn(#"Extracted Values1", "City", each Table.Column([Count],"City")),
    #"Extracted Values2" = Table.TransformColumns(#"Added Custom2", {"City", each Text.Combine(List.Transform(_, Text.From), "; "), type text}),
    #"Added Custom3" = Table.AddColumn(#"Extracted Values2", "State", each Table.Column([Count],"State")),
    #"Extracted Values3" = Table.TransformColumns(#"Added Custom3", {"State", each Text.Combine(List.Transform(_, Text.From), "; "), type text}),
    #"Added Custom4" = Table.AddColumn(#"Extracted Values3", "Zip", each Table.Column([Count],"Zip")),
    #"Extracted Values4" = Table.TransformColumns(#"Added Custom4", {"Zip", each Text.Combine(List.Transform(_, Text.From), "; "), type text}),
    #"Added Custom5" = Table.AddColumn(#"Extracted Values4", "Reason For Return", each Table.Column([Count],"Reason For Return")),
    #"Extracted Values5" = Table.TransformColumns(#"Added Custom5", {"Reason For Return", each Text.Combine(List.Transform(_, Text.From), "; "), type text}),
    #"Added Custom6" = Table.AddColumn(#"Extracted Values5", "Quatity Returned", each Table.Column([Count],"Quantity Returned")),
    #"Extracted Values6" = Table.TransformColumns(#"Added Custom6", {"Quatity Returned", each Text.Combine(List.Transform(_, Text.From), "; "), type text}),
    #"Added Custom7" = Table.AddColumn(#"Extracted Values6", "SKU", each Table.Column([Count],"SKU")),
    #"Extracted Values7" = Table.TransformColumns(#"Added Custom7", {"SKU", each Text.Combine(List.Transform(_, Text.From), "; "), type text}),
    #"Added Custom8" = Table.AddColumn(#"Extracted Values7", "Product", each Table.Column([Count],"Product")),
    #"Extracted Values8" = Table.TransformColumns(#"Added Custom8", {"Product", each Text.Combine(List.Transform(_, Text.From), "; "), type text})
in
    #"Extracted Values8"[/SIZE]

Table1
Order Number	Customer Name	Address1	City	State	Zip	Reason For Return	Quantity Returned	SKU
99323462​	Maggie Curry	1234 Happy Street	Dallas	TX	75212​	RTS - Return To Sender	1​	1001​
99323462​	Maggie Curry	1235 Happy Street	Dallas	TX	75212​	RTS - Return To Sender	2​	1002​
99323463​	Karen Jones	1236 Jones Road	Addison	Tx	75213​	RTS - Return To Sender	3​	1003​
99323464​	Ben Smith	1237 Willow Way	Plano	TX	75214​	RTS - Return To Sender	1​	105​
99323465​	Maggie Curry	1238 Happy Street	Dallas	TX	75212​	RTS - Return To Sender	1​	1006​
Table2
SKU	Product
1001​	Duffel Bags
1002​	Protein Shake
1003​	Vitamins
1005​	Blender Bottle
1006​	T-Shirt
Result
Customer Name	Order Number	Address1	City	State	Zip	Reason For Return	Quatity Returned	SKU	Product
Maggie Curry	99323462; 99323462; 99323465	1234 Happy Street; 1235 Happy Street; 1238 Happy Street	Dallas; Dallas; Dallas	TX; TX; TX	75212; 75212; 75212	RTS - Return To Sender; RTS - Return To Sender; RTS - Return To Sender	1; 2; 1	1001; 1002; 1006	Duffel Bags; Protein Shake; T-Shirt
Karen Jones	99323463	1236 Jones Road	Addison	Tx	75213	RTS - Return To Sender	3	1003	Vitamins
Ben Smith	99323464	1237 Willow Way	Plano	TX	75214	RTS - Return To Sender	1	105

You can change headers name in a last step if you want
and you can add List.Distinct there where you want
all above is an example of solution

 

[maggiec27]

Does the SKU Column need to be the last column in table 1 or can it be anywhere?

 

[sandy666]

No!, of course not. You can move (drag) every column of source there where you want but before Table1 will be loaded into PQ
or move any column after load into PQ in PQ editor

 

[sandy666]

I moved SKU of Table1 from the end to the second position and as you can see result didn't change

Table1
Order Number	SKU	Customer Name	Address1	City	State	Zip	Reason For Return	Quantity Returned
99323462​	1001​	Maggie Curry	1234 Happy Street	Dallas	TX	75212​	RTS - Return To Sender	1​
99323462​	1002​	Maggie Curry	1235 Happy Street	Dallas	TX	75212​	RTS - Return To Sender	2​
99323463​	1003​	Karen Jones	1236 Jones Road	Addison	Tx	75213​	RTS - Return To Sender	3​
99323464​	105​	Ben Smith	1237 Willow Way	Plano	TX	75214​	RTS - Return To Sender	1​
99323465​	1006​	Maggie Curry	1238 Happy Street	Dallas	TX	75212​	RTS - Return To Sender	1​
Table2
SKU	Product
1001​	Duffel Bags
1002​	Protein Shake
1003​	Vitamins
1005​	Blender Bottle
1006​	T-Shirt
Result
Customer Name	Order Number	Address1	City	State	Zip	Reason For Return	Quatity Returned	SKU	Product
Maggie Curry	99323462; 99323462; 99323465	1234 Happy Street; 1235 Happy Street; 1238 Happy Street	Dallas; Dallas; Dallas	TX; TX; TX	75212; 75212; 75212	RTS - Return To Sender; RTS - Return To Sender; RTS - Return To Sender	1; 2; 1	1001; 1002; 1006	Duffel Bags; Protein Shake; T-Shirt
Karen Jones	99323463	1236 Jones Road	Addison	Tx	75213	RTS - Return To Sender	3	1003	Vitamins
Ben Smith	99323464	1237 Willow Way	Plano	TX	75214	RTS - Return To Sender	1	105

 

[maggiec27]

Looks good. I was having issues with setting up the two tables and creating the merge. Good practice and learned alot doing it. But I finally got it the table working as you have described. I replicated each step manually to get a feel for how the editor works and used your code as a reference. Get and Transform is a really cool feature, incredibly useful. Right now I have just been practicing with some sample data, so I've just been using a small sample of data on my current workbook.

In the future, I will receive a new file daily with all of the return information while the SKU and product name file stays the same. What is the best way to go about transforming the data?

Do I save this workbook I've been using and from it, 'get data', get from file, then load my query or is that going to screw everything up since I did all of this test work using 'tables'?

 

[sandy666]

Table1 = will be changed
Table2 = unchanged

new table append (not append as new!) to the table1, refresh result table (theory - because I don't know real structure of your data)

you can load tables from the same workbook (sheets), from other workbooks.... there is many sources. Check NewQuery option.

edit:
all steps are on the right side of the PQ Editor, with a little gear on the end of almost every step, where you can see sub-steps

btw. merging tables is like vlookup or index/match