Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
ArnoldRKok
New Member

find a partner name based on customer id in another table

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]
Partner112345,44321
Partner223456
Partner3 
Partner445654

 

Desired result

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

 

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

1 ACCEPTED SOLUTION
VasTg
Memorable Member
Memorable Member

@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

Connect on LinkedIn

View solution in original post

4 REPLIES 4
Jimmy801
Community Champion
Community Champion

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

grafik.png

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

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
Memorable Member
Memorable Member

@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

Connect on LinkedIn

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors