Adding new columns from rows with the same unique key value

Great use case! Easiest, most robust way in Power BI is Power Query (M): add a per-key index, then pivot both ChargeCode and LineCharge into numbered columns.

1. Load your table (say it’s named Charges) into Power Query.
2. Sort by ProUK, then (optionally) by ChargeCode to control column order.
3. Add a per-ProUK index: Group by ProUK and add an index starting at 1.
4. Create “attribute–value” rows for both fields (ChargeCode/LineCharge) with the index appended (ChargeCode1, LineCharge1, …).
5. Pivot the Attribute column.
6. Close & Apply.

M code (paste into a blank query, update Source if needed)

let
Source = Charges, // <-- your table name
#"Typed" = Table.TransformColumnTypes(Source,
{{"ProUK", Int64.Type}, {"ChargeCode", Int64.Type}, {"LineCharge", type number}}),

#"Sorted" = Table.Sort(#"Typed", {{"ProUK", Order.Ascending}, {"ChargeCode", Order.Ascending}}),

// Add per-key index
#"Grouped" = Table.Group(#"Sorted", {"ProUK"},
{{"t", each Table.AddIndexColumn(_, "Pos", 1, 1)}}),
#"Expanded" = Table.ExpandTableColumn(#"Grouped", "t", {"ChargeCode","LineCharge","Pos"}),

// Build attribute/value rows for ChargeCodeN
CC1 = Table.TransformColumns(#"Expanded", {{"Pos", each "ChargeCode" & Text.From(_), type text}}),
CC2 = Table.RenameColumns(CC1, {{"Pos","Attribute"}, {"ChargeCode","Value"}}),
CC3 = Table.SelectColumns(CC2, {"ProUK","Attribute","Value"}),

// Build attribute/value rows for LineChargeN
LC1 = Table.TransformColumns(#"Expanded", {{"Pos", each "LineCharge" & Text.From(_), type text}}),
LC2 = Table.RenameColumns(LC1, {{"Pos","Attribute"}, {"LineCharge","Value"}}),
LC3 = Table.SelectColumns(LC2, {"ProUK","Attribute","Value"}),

// Combine and pivot
Unioned = Table.Combine({CC3, LC3}),
Pivoted = Table.Pivot(Unioned, List.Distinct(Unioned[Attribute]), "Attribute", "Value")
in
Pivoted

Result
You’ll get columns like:

ProUK | ChargeCode1 | LineCharge1 | ChargeCode2 | LineCharge2 | ChargeCode3 | LineCharge3 | ...

For your sample:

2264077 | 100 | 296.31 | 5000 | 139.44
2264078 | 100 | 556 | 5000 | 155.68 | 50 | 329.44

I hope it will help

Hi,

This M code in Power Query works

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"ProUK"}, {{"Count", each Table.AddIndexColumn(_,"Index",1)}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"ChargeCode", "LineCharge", "Index"}, {"ChargeCode", "LineCharge", "Index"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Expanded Count", {"ProUK", "Index"}, "Attribute", "Value"),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"Index", type text}}, "en-IN"),{"Attribute", "Index"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value")
in
    #"Pivoted Column"

Hope this helps.