Forum Discussion
Grouping data with values spread in different columns.
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.
2 Replies
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