Forum Discussion

jeffw14's avatar
jeffw14
Icon for Helper I rankHelper I
1 year ago
Solved

Adding new columns from rows with the same unique key value

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 ...
Need Help
  • Ilgar_Zarbali's avatar
    Ilgar_Zarbali
    1 year ago

    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

     

     

     

     

     

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    1 year ago

    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.

     

Shahid12523's avatar
Shahid12523
Icon for Community Champion rankCommunity Champion
1 year ago

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.