Simple Power Query Pivot Question (I think!)

[denzo36]

Hi guys,

I'm pretty experienced with Excel but still a novice in Power Query so apologies in advance if this question is trivial for some but I can seem to find the solution anywhere.

I deal a lot with multiple policy numbers and which in turn can have multiple claim numbers. These are always listed in my data in rows, so each row will have a policy number and a claim number. For policies with multiple claims, each claim number is on a new row with the same policy number beside each claim. So for example, a policy with 4 claims will have 4 rows in my data, the same policy number on each row and the 4 different claim numbers listed on the row. The image may explain it better.

I'm trying to pivot this in some way so that I have one row for every policy number with the associated claim numbers pivoted in columns on the same row. I've tried pivot other columns etc but can't seem to get it to do what I want.

Could somebody steer me in the right direction?

 

[sandy666]

try

Policy	Claim		Policy	Claim.1	Claim.2	Claim.3	Claim.4
P123456	C11223344		P123456	C11223344	C99887766	C12365490	C98765432
P123457	C2114433		P123457	C2114433	C5147821
P123458	C55443322		P123458	C55443322	C59871258
P123456	C99887766
P123456	C12365490
P123458	C59871258
P123457	C5147821
P123456	C98765432

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Policy"}, {{"CNT", each Table.RowCount(_), type number}, {"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Claim", each [Count][Claim]),
    Extract = Table.TransformColumns(List, {"Claim", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    Split = Table.SplitColumn(Extract, "Claim", Splitter.SplitTextByAnyDelimiter({","}, QuoteStyle.Csv),List.Max(Group[CNT])),
    TSC = Table.SelectColumns(Split,{"Policy", "Claim.1", "Claim.2", "Claim.3", "Claim.4"})
in
    TSC

 

[denzo36]

Awesome, thanks works a treat, love the way Power Query allows you to check each step as well so I can work out what you did.

Much appreciated, cheers!

 

[sandy666]

I am glad you are HAPPY
thanks for the feedback