Linking related items within the same table-Power Query

[mraka9]

I need help with Power Query.
I have a table with columns: A, B and C
Column A and C contain identical items to be linked.
I want column C to check where everything is in column A and show concatenated in column D.
I hope that is clear enough.
I did it by duplicating the tables and then merging them, but it's a complex query and consumes too many resources, so I'd like it all to be done in the same table.

Thanks in advance!

A	B	C	D
AAA	456	BBB	777;258
BBB	777	AAA	456;111
CCC	369	CCC	369;750
AAA	111	BBB	777;258
BBB	258	CCC	369;750
CCC	750	AAA	456;111

[TomMartens]

Hey @mraka9 ,

if you change this line

Table.SelectRows( #"Changed Type" , each [C] = filtervalue )[B]

to this

Table.SelectRows( #"Changed Type" , each [A] = filtervalue )[B]

the function returns what you are looking for:

Regards,
Tom

[mraka9]

Hello Tom,

can you customize the function?
Column B is in text form for me. It is not necessary to change the garden in operation.

Although everything in the example is mostly shown as a number, there is also text.

Apologies and thank you very much in advance!

[TomMartens]

Hey @mraka9 ,

change this line

, each Number.ToText( _ )

to this

, each  _

Regards,

Tom

[mraka9]

Greeting,
it's amazing but I'm still struggling with this problem. You have found a solution but when I try to apply it to my large database I have a problem. I don't know if the problem is that everything is in Croatian...
When I add a custom column as you said, the column is created for me and it says "Function".

I don't know what I'm doing wrong.

This is the code I entered:

= Table.AddColumn(#"Dupliciran stupac1", "Prilagođeno", each ( row ) =>
let
filtervalue = row[#"Kataloški broj i ponuditelj - kopija"],
combinedString =
Text.Combine(
List.Transform(
Table.SelectRows( #"Dupliciran stupac1" , each [Kataloški broj i ponuditelj] = filtervalue) [#"Kataloški broj i ponuditelj-srodni"]
, each _
)
, ";")
in
combinedString)

REMARK:

In the example I added that the columns are called A B and C, but in reality they are columns:

A = Kataloški broj i ponuditelj
B = Kataloški broj i ponuditelj-srodni
C = Kataloški broj i ponuditelj - kopija

Thank you very much in advance

[mraka9]

Thanks Tom!
I will try to do it that way, I hope it will work then.
nice greetings,

[mraka9]

Hello Tom,

can you upload the pbix file.
The language in Power BI is not English for me, so I can't just copy.
Thank you very much. That might be the solution I'm looking for!

[TomMartens · Accepted solution]

Hey @mraka9 , 

here you will find the pbix file: https://tommartens-my.sharepoint.com/:u:/g/personal/tom_minceddata_com/EblF6V1lDU9Juds8936QHn4Bv2SHq...

Regards,
Tom

[mraka9]

Well done!
Thank you very much Tom!

Greetings

[TomMartens]

Hey @mraka9 ,

to create this

I created an inline function using the advanced editor, the code below added the custom column "Custom", which holds the result, the combinedString:

...
    #"Added Custom" = 
        Table.AddColumn(#"Changed Type", "Custom", 
        ( row ) => 
            let
            filtervalue = row[C],
            combinedString = 
                Text.Combine(
                    List.Transform(
                        Table.SelectRows( #"Changed Type" , each [C] = filtervalue )[B]
                        , each Number.ToText( _ )
                    )
                , ";") 
            in
            combinedString
        )
in
    #"Added Custom"

Hopefully, this provides what you are looking for. If not, consider creating a pbix that contains sample data, upload the pbix to onedrive, googledrive, or dropbox and share the link. If you are using Excel to create the sample data share the xlsx as well.

Regards,
Tom

[mraka9]

Unfortunately, the result is not correct. Column D should contain the items as shown in the example.
Thanks anyway!