Need help with Power Query Joins

[Milperty]

Hi All,

I am using power query to join two tables using left outer join. The problem is I am having 2 columns in one table to join or lookup for one column in another table which is not allowing in power query.
I have sample data here. Please help me with this.

The thing I am trying to achieve lookup up the values present in the column 1 and 2 in Table 1 in the Column-2 in Table 2. The approach I am following might not be perfect. I appreciate if someone can help with this or can suggest any better approach using power query or powerpivot dax. Thank you!

 

[sandy666]

Use XL2BB and post copyable example

 

[sandy666]

anyway, adapt this

Column1	Column2		Column1		Result
Red	Green		Red		Red,Green
Yellow	Green		Green		Green
Blue	Pink		Blue		Blue

let
    Source = Excel.CurrentWorkbook(){[Name="TableB"]}[Content],
    Expand = Table.ExpandTableColumn(Table.AddColumn(Source, "Custom", each TableA), "Custom", {"Column1", "Column2"}, {"Column1.1", "Column2"}),
    CC = Table.AddColumn(Table.Distinct(Expand, {"Column1"}), "Custom", each if [Column1] = [Column1.1] then [Column2] else null),
    TSC = Table.SelectColumns(Table.AddColumn(CC, "Result", each Text.Combine({[Column1], [Custom]}, ","), type text),{"Result"})
in
    TSC

imo, enough to change colours to your data
all is done with PQ Editor (nothing manual) ?