cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
TaniaR Regular Visitor
Regular 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 Regular Visitor
Regular 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
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors