Forum Discussion

branners's avatar
branners
New Member
5 years ago
Solved

Splitting row into 2 rows based on field value

Hi   Been going around in circles here, really hope someone can help.   We have potentially 2 sales people on a given deal. If there is a second salesperson, they should be created as an addition...
Need Help
Ashish_Mathur's avatar
Ashish_Mathur
Super User
5 years ago

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index", "Revenue"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Index"}, {{"GroupTables", each _, type table [Revenue=number, Index=number, Attribute=text, Value=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Divisor", each Table.RowCount([GroupTables])),
    #"Expanded GroupTables" = Table.ExpandTableColumn(#"Added Custom", "GroupTables", {"Revenue", "Attribute", "Value"}, {"Revenue", "Attribute", "Value"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded GroupTables", "Custom", each [Revenue]/[Divisor]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Index", "Divisor", "Attribute", "Revenue"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Revenue"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Revenue", type number}})
in
    #"Changed Type"

Hope this helps.