find a partner name based on customer id in another table

[ArnoldRKok]

Hi,

I am quite new with Power Query in Excel. Sorry if I am asking newbee questions 🙂

I have two tables (created from 2 csv-files).

Table1 is called customer and has among some other columns a column called "Customers" containing a 5 digit customer id.

Table2 is called partners and has among some other columns a column called "Name" with the name of the partner (text field) and a column called "Customers" with a comma-separated list of customer ids. This "Customers" column can include blanks, 1 customer id or even over 20 customer id's

I want to add a column to my customer table (Table1) where the corresponding partner name is displayed.

Example:

Table1 customer

customer[Customer]

12345

23456

44321

45654

Table2 partners

partners[Name]	partners[Customers]
Partner1	12345,44321
Partner2	23456
Partner3
Partner4	45654

Desired result

customer[Customer]	customer[Partner]
12345	Partner1
23456	Partner2
44321	Partner1
45654	Partner4

Below VBA-function (I would have to place it in each cell in the Partners column) gets the result I want, but I rather have this integrated in the power query:

Function AddPartner(strSearch As String)
   Dim sPartner As Worksheet
   Dim list As ListObject
   Dim cell As Range

   Set sPartner = Sheets("partners")
   Set list = sPartners.ListObjects("Table2")

   'search in any cell of the data range of excel table
   Set cell = list.ListColumns("Customers").DataBodyRange.Find(strSearch)

   If cell Is Nothing Then
      Debug.Print strSearch, "information is not found"
      AddPartner= Null
   Else
      AddPartner = cell.Offset(0, -1)
   End If

End Function

Can you please advise?

Thanks, Arnold

[Jimmy801]

Hello @ArnoldRKok 

you can add a Table.TransformColumns and apply a Text.Split(_,","). Afterwards Expand the created list and change the type to numbers. After you are able to join them.

Here the complete code to check the solution

let
    Customer = 
    let
		Source = #table
		(
			{"Customer"},
			{
				{"12345"},	{"23456"},	{"44321"},	{"45654"}
			}
		),
		ChangeType = Table.TransformColumnTypes(Source,{{"Customer", Int64.Type}})
    in
        ChangeType,
	Partners = 
	let
		Source = #table
		(
			{"Name","Customers"},
			{
				{"Partner1","12345,44321"},	{"Partner2","23456"},	{"Partner3",""},	{"Partner4","45654"}
			}
		),
		SplitCustomers = Table.TransformColumns
		(
			Source,
			{
				{
					"Customers",
					each Text.Split(_,",")
				}
			}
		),
		ExpandList = Table.ExpandListColumn
		(
			SplitCustomers,
			"Customers"
		),
		ChangeType = Table.TransformColumnTypes(ExpandList,{{"Customers", Int64.Type}})
		

	in
		ChangeType,
	
	Join = Table.NestedJoin
	(
		Customer,
		"Customer",
		Partners,
		"Customers",
		"Partners"
	),
    Expand = Table.ExpandTableColumn(Join, "Partners", {"Name"}, {"Name"})
in 
    Expand

this is most important part

Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[ArnoldRKok]

Hi,

Splitting the columns into other columns using the wizard gave an error.

I like your example. Will surely copy and study it later, but for now I have a workable solution using Vastg's suggestion, but I will learn a lot from your suggested code 🙂

Thank you very much for your suggestion.

Kind regards, Arnold

[VasTg · Accepted solution]

@ArnoldRKok 

You have to split the column into rows with comma delimiter and then define the relationship between the tables based on Customer.(Customer to Partners - 1 to Many)

Either you could create a column to in customers as max(partners[Name]) or just directly use that in a visual/table.

Refer to this post as it give step by step instruction on how to use power query for this ask.

https://community.powerbi.com/t5/Desktop/Split-comma-delimited-cell-into-multiple-rows-keeping-row/t...

If it helps, mark it as a solution

Kudos are nice too

[ArnoldRKok]

Splitting the column into new columns gave an error, but your suggestion to split into rows works beautifully.

Now I can merge the two sources together.

Thanks 🙂

Arnold