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.

     

Royel's avatar
Royel
Icon for Super User rankSuper User
1 year ago

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!