Adding new columns from rows with the same unique key value

Hi jeffw14  you can use this m-code in Power Query to get your expected results 

Quick Guide: Power BI Desktop -> Transform Data -> Blank Query -> Advance Editor -> Pest the code. 

let
    // Step 1: Start with your source table
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZcxLCsAgEAPQu8xahvmrZxHvfw2HFsXSTQg8kjFAJIxqhQJMlCk9UBlmucnpMdaOZpe1M3MPzPKlPXPHaD/LUHkP5wI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProUK = _t, ChargeCode = _t, LineCharge = _t]),
    #"ChangedType" = Table.TransformColumnTypes(Source,{{"ProUK", Int64.Type}, {"ChargeCode", Int64.Type}, {"LineCharge", type number}}),
    
    // Step 2: Group by ProUK and add an index to number the rows within each group
    GroupedRows = Table.Group(ChangedType, {"ProUK"}, {
        {"AllRows", each Table.AddIndexColumn(_, "RowIndex", 1), type table}
    }),
    
    // Step 3: Expand the grouped table
    ExpandedTable = Table.ExpandTableColumn(GroupedRows, "AllRows", 
        {"ChargeCode", "LineCharge", "RowIndex"}, 
        {"ChargeCode", "LineCharge", "RowIndex"}),
    
    // Step 4: Create column names with suffix based on RowIndex
    AddColumnNames = Table.AddColumn(ExpandedTable, "ChargeCodeCol", 
        each if [RowIndex] = 1 then "ChargeCode" else "ChargeCode" & Text.From([RowIndex])),
    AddLineChargeNames = Table.AddColumn(AddColumnNames, "LineChargeCol", 
        each if [RowIndex] = 1 then "LineCharge" else "LineCharge" & Text.From([RowIndex])),
    
    // Step 5: Create a helper column for pivot
    CombineForPivot = Table.AddColumn(AddLineChargeNames, "PivotHelper", 
        each [ChargeCodeCol] & "|" & [LineChargeCol]),
    
    // Step 6: Pivot the ChargeCode values
    PivotChargeCode = Table.Pivot(
        Table.SelectColumns(CombineForPivot, {"ProUK", "ChargeCodeCol", "ChargeCode"}), 
        List.Distinct(CombineForPivot[ChargeCodeCol]), 
        "ChargeCodeCol", 
        "ChargeCode"
    ),
    
    // Step 7: Pivot the LineCharge values  
    PivotLineCharge = Table.Pivot(
        Table.SelectColumns(CombineForPivot, {"ProUK", "LineChargeCol", "LineCharge"}), 
        List.Distinct(CombineForPivot[LineChargeCol]), 
        "LineChargeCol", 
        "LineCharge"
    ),
    
    // Step 8: Merge the two pivoted tables
    MergedResult = Table.NestedJoin(PivotChargeCode, {"ProUK"}, PivotLineCharge, {"ProUK"}, "LineChargeData", JoinKind.Inner),
    
    // Step 9: Expand the LineCharge columns
    LineChargeColumns = Table.ColumnNames(PivotLineCharge),
    FilteredLineChargeColumns = List.Select(LineChargeColumns, each _ <> "ProUK"),
    
    FinalResult = Table.ExpandTableColumn(MergedResult, "LineChargeData", FilteredLineChargeColumns, FilteredLineChargeColumns),
    #"Changed Type" = Table.TransformColumnTypes(FinalResult,{{"ChargeCode", Int64.Type}, {"ChargeCode2", Int64.Type}, {"ChargeCode3", Int64.Type}, {"LineCharge", type number}, {"LineCharge2", type number}, {"LineCharge3", type number}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"ProUK", "ChargeCode", "LineCharge", "ChargeCode2", "LineCharge2", "ChargeCode3", "LineCharge3"})
in
    #"Reordered Columns"

Find this helpful? ✔ Give a Kudo • Mark as Solution – help others too!

DAX-only pattern that pivots rows into numbered columns.

1-Add a per-key position in Charges (calculated column)

Sort order: by ChargeCode (change if you prefer another order).

Pos =
RANKX(
FILTER(Charges, Charges[ProUK] = EARLIER(Charges[ProUK])),
Charges[ChargeCode],
,
ASC,
DENSE
)

2-Create the wide table (calculated table)

Adjust N (here N=3) by adding more pairs.

Charges_Wide =
ADDCOLUMNS(
SUMMARIZE(Charges, Charges[ProUK]),
"ChargeCode1", CALCULATE(SELECTEDVALUE(Charges[ChargeCode]), Charges[Pos] = 1),
"LineCharge1", CALCULATE(SELECTEDVALUE(Charges[LineCharge]), Charges[Pos] = 1),
"ChargeCode2", CALCULATE(SELECTEDVALUE(Charges[ChargeCode]), Charges[Pos] = 2),
"LineCharge2", CALCULATE(SELECTEDVALUE(Charges[LineCharge]), Charges[Pos] = 2),
"ChargeCode3", CALCULATE(SELECTEDVALUE(Charges[ChargeCode]), Charges[Pos] = 3),
"LineCharge3", CALCULATE(SELECTEDVALUE(Charges[LineCharge]), Charges[Pos] = 3)
)

Result
One row per ProUK with ChargeCode1/LineCharge1, ChargeCode2/LineCharge2, etc.
If a key has fewer rows, missing pairs will be blank.

You need to pivot rows into columns.

SQL way:

WITH cte AS (
SELECT ProUK, ChargeCode, LineCharge,
ROW_NUMBER() OVER(PARTITION BY ProUK ORDER BY ChargeCode) rn
FROM YourTable
)
SELECT ProUK,
MAX(CASE WHEN rn=1 THEN ChargeCode END) AS ChargeCode1,
MAX(CASE WHEN rn=1 THEN LineCharge END) AS LineCharge1,
MAX(CASE WHEN rn=2 THEN ChargeCode END) AS ChargeCode2,
MAX(CASE WHEN rn=2 THEN LineCharge END) AS LineCharge2,
MAX(CASE WHEN rn=3 THEN ChargeCode END) AS ChargeCode3,
MAX(CASE WHEN rn=3 THEN LineCharge END) AS LineCharge3
FROM cte
GROUP BY ProUK;

Power Query: Group by ProUK → add index → pivot ChargeCode & LineCharge by index.

Pandas: Use groupby().cumcount() to create index → pivot wider.

Result: one row per ProUK with ChargeCode1/LineCharge1, ChargeCode2/LineCharge2, etc.