Aggregating certain rows with power query?

[jplank]

I'm new to Power Query and have what might be a basic question. I have my data in the following format:

Company	Sales
Company A	100
Company B (division 1)	200
Company B (division 2)	300
Company B (division 3)	400
Company C	500
Company D	600
Company E (division 1)	100
Company E (division 2)	200

Is there a way to transform my data so that Company E divisions 1 and 2 are aggregated into a Company E row and similarly with Company B divisions 1, 2 and 3?

I've cross-posted this question here:

https://www.reddit.com/r/excel/comments/emsd8e

Aggregating certain rows with power query?

I'm new to Power Query and have what might be a basic question. I have my data in the following format: Company Sales Company A 100 Company B (division 1) 200 Company B (division 2) 300 Company B (division 3) 400 Company C 500...

www.excelforum.com

 

[sandy666]

 

[jplank]

#1 is what I'm looking for. The actual information about divisions is not relevant for my analysis.

 

[sandy666]

#1 is what I'm looking for

sure

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Company", type text}, {"Sales", Int64.Type}}),
    ETBD = Table.TransformColumns(Type, {{"Company", each Text.BeforeDelimiter(_, " ("), type text}}),
    Group = Table.Group(ETBD, {"Company"}, {{"Sales", each List.Sum([Sales]), type number}})
in
    Group

 

[jplank]

Probably a dumb question, but where do I put that code? And how to I execute it?

 

[sandy666]

Select you range and use Ctrl+T
Be sure the name of the new table is the same as in the M-code (here: Table1)
select any cell in the new Table and:
- Data - From Table
- Advanced Editor
- replace whole code there with copied from the post
- Done
- Close&Load

 

[jplank]

Thank you

 

[sandy666]

You are welcome

Have a nice day