Forum Discussion
List with duplicates to columns
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.
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 PivotedColumncheers,
S
2 Replies
- sturlaws
Resident Rockstar
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 PivotedColumncheers,
S