Grouping data with values spread in different columns.

[Anonymous]

Hi.

I am quite new in Power Query and would like to know how to join/ unite data by Sales Person Code with values that are spread in diferent columns. ( please see photo attached).

So, basically I want to have a column with the sum of Sales made by each  Sales person.

It is a split sales,(there can be like 5 level split) for instance Scot has sold a product 100% himself. Angelo and Bern have it joint.. Angelo contribute with 30% and Bern 20% on the same product. So i want the  new column to be able to calculate the amout of each person based on their contribution.

So, I was thinking of Combining the SALES PERSON CODE column (which as you can see can be 2 or more columns) and then bring the Sales amount.

Can anyone help know how can I combine different columns ?

Thank you in advance.

[Anonymous]

Hi Tania,

I changed a little your input table so it was more clear (at least to me) to get your inputs.

Later, I created a query with the following code to consolidate the results by Sales Rep.

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sale #", Int64.Type}, {"Sale Amount", Int64.Type}, {"Scott", Int64.Type}, {"Angelo", type number}, {"Bern", type number}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Sale #", "Sale Amount"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Value", "% of Sales"}, {"Attribute", "Sales Rep"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"% of Sales", Percentage.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Sales Amount by Rep", each [Sale Amount]*[#"% of Sales"]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"Sales Amount by Rep", type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Sale Amount"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Sales Rep"}, {{"Sales by Rep", each List.Sum([Sales Amount by Rep]), type number}})
in
    #"Grouped Rows"

The input and results are as below:

You can add as many new reps in the columns as you want, and you can also add percentages in multiple columns.

I hope that works!

Please mark it as solved if that is the case.

Vagner

[Anonymous]

Hi Kogikoski. 

I am not sure that's the case. This is a huge file .. sales rep code comes by column-vertical. Your way implies reordering the entire columns if not the entire table. 

Do you think there may be other way?