List with duplicates to columns

[Anonymous]

Hi guys,

I've a very simple question, but I can't figure it out myself. I have a list with two columns with in the first one numbers with duplicate values and in the second column letters that belong to that number. I want to create a column for each of this letter.

Nr                  Letter

1                       A

2                       A

2                       B

3                       A

I want;

Nr               Column1   Column2    ....

1                       A

2                       A                B

3                       A

How can I achieve this in Power BI? Or Excel, that would do the trick to.

[sturlaws · Accepted solution]

Hi,

using this post of how to create a partition index, you can write this in the advanced editor of power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJUitWJVjJCYTmBWcYQsVgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Nr = _t, Letter = _t]),
    
    Partition = Table.Group(Source, {"Nr"}, {{"Partition", each _, type table}}),

    AddedCustom = Table.AddColumn(Partition, "Custom", each Table.AddIndexColumn([Partition], "Index", 1,1)),

    RemovedColumns = Table.RemoveColumns(AddedCustom,{"Partition"}),

    ExpandedCustom = Table.ExpandTableColumn(RemovedColumns, "Custom", {"Letter", "Index"}, {"Letter", "Index"}),
    
    PivotedColumn = Table.Pivot(Table.TransformColumnTypes(ExpandedCustom, {{"Index", type text}}, "nb-NO"), List.Distinct(Table.TransformColumnTypes(ExpandedCustom, {{"Index", type text}}, "nb-NO")[Index]), "Index", "Letter")

in

    PivotedColumn

cheers,

S

[Anonymous]

Great solution! Easy to use and works perfectly.

Much better than the Excel solutions.