cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TaniaR Frequent Visitor
Frequent Visitor

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.

poer bi question.PNG

2 REPLIES 2
Kogikoski Frequent Visitor
Frequent Visitor

Re: Grouping data with values spread in different columns.

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:

 

Annotation 2019-08-21 123656.png

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

TaniaR Frequent Visitor
Frequent Visitor

Re: Grouping data with values spread in different columns.

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?

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 306 members 3,273 guests
Please welcome our newest community members: