power query lookup table with duplicates

[adamelston]

Hello, I am trying to make a lookup table to link two tables but there are duplicates in both tables. I think what I need is to ensure unique rows and have any duplicate lookup values in columns. If my first table is as below:

ID	Region
x	North
y	South
x	East
z	West
x	West

I think I need to create a table like this:

ID	Region 1	Region 2
x	North	East
y	South
z	North	West

I've been messing around with pivots in power query but can't seem to create second table. Please could you let me know how best to do this?
Many thanks,
A

 

[sandy666]

maybe

ID	Region		ID	Region.1	Region.2	Region.3
x	North		x	North	East	West
y	South		y	South
x	East		z	West
z	West
x	West

with Power Query aka Get&Transform

let
    Source = Excel.CurrentWorkbook(){[Name="Table10"]}[Content],
    Grp = Table.Group(Source, {"ID"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Grp, "Region", each [Count][Region]),
    Extract = Table.TransformColumns(List, {"Region", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    Split = Table.SplitColumn(Extract, "Region", Splitter.SplitTextByAnyDelimiter({","}, QuoteStyle.Csv))
in
    Split

 

[adamelston]

Many thanks for quick response. That looks like it would do it but I am not sure how to use that code to achieve what I need. I am still at the point and click stage with power bi/dax. Do you know if there is a menu selection for this in power query?!
A

 

[sandy666]

update your profile about Excel version

Alt A PN O Q and in new window Alt H Q then replace code there with copied from the post
your source data should be an Excel Table (Ctrl T)

 

[adamelston]

Thanks, I will give this a try. I am working in power query.

 

[sandy666]

but would be fine if you'll update your profile about Excel version

 

[adamelston]

update your profile about Excel version

Alt A PN O Q and in new window Alt H Q then replace code there with copied from the post
your source data should be an Excel Table (Ctrl T)

Thanks for your help. This works for my example data but not quite for my real data (with relevant table names/headings changed). I will keep trying but this was very useful.

 

[sandy666]

so post representative source data with original structure and generic values if necessary