Transforming data help

[user900]

My data looks like this:

Name	Policy
A	105
A	106
B	105
C	105
C	205

I want to show in a table as this:

A	105	106
B	105
C	105	205

How do I do this?

Thanks in advance.

[Anonymous · Accepted solution]

Hi @user900 

You can put the following code to advanced editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MFWK1YGxzcBsJyRxZzS2EYgdCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Policy = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Policy", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"Count", each Table.AddIndexColumn(_,"Index",1,1),type table}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Policy", "Index"}, {"Policy", "Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Count", {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expanded Count", {{"Index", type text}}, "en-US")[Index]), "Index", "Policy")
in
    #"Pivoted Column"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlienSx]

let
    Source = your_data,
    g = Table.Group(Source, "Name", {{"rows", each {[Name]{0}} & [Policy]}}),
    to_tbl = Table.FromList(g[rows], (x) => x, List.Count(List.Max(g[rows], null, List.Count)))
in
    to_tbl