Adding new columns from rows with the same unique key value

[jeffw14]

Have a table with Unique key value, one row for each charge code and Line charge. Need to combine the multiple rows into one row per unique key but have the other row values for charge code and line charge be new columns on that row. 

Date looks like this now:

ProUK	ChargeCode	LineCharge
2264077	100	296.31
2264077	5000	139.44
2264078	100	556
2264078	5000	155.68
2264078	50	329.44

Need it to look like this:

ProUK	ChargeCode	LineCharge	ChargeCode2	LineCharge2	ChargeCode3	LineCharge3
2264077	100	296.31	5000	139.44
2264078	100	556	5000	155.68	50	329.44

[Ilgar_Zarbali · Accepted solution]

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

[Shahid12523]

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.

[Ashish_Mathur · Accepted solution]

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.

[Ilgar_Zarbali]

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.

[Royel]

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!

[Ilgar_Zarbali · Accepted solution]

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