How to Merge Duplicate Rows and Sum Numeric Values in Power BI When Cleaning Sales Data from Multiple Regions?

I have taken up this course "Data Transformation in Power BI" on a website called 'data camp', and here I am stuck in this task where I am supposed to clean the sales data given to me by using transpose and unpivot features and find out what was the largest sales value transacted with a company in one month.

here is a link to datacamp to download/view the file: (https://s3.amazonaws.com/assets.datacamp.com/production/repositories/6048/datasets/Exercises+and+Datasources/data-transformation-in-power-bi.zip) file path: Datasets> Sales_1997

emp ID	region	company name	year	month	total sales
1	Eastern	Antonio Moreno	1997	April	456
3	Eastern	Antonio Moreno	1997	Sept	521
5	Southern	Antonio Moreno	1997	Sept	541
9	Western	Around the Horn	1997	Jan	410
8	Northern	Around the Horn	1997	May	102
1	Southern	Around the Horn	1997	Jan	132
6	Eastern	Around the Horn	1997	Jan	245

I first demoted my headers and transposed my table. then I individually merged the rows and transposed them back to their original form. here is my result with the code:

=Table.CombineColumns(Table.TransformColumnTypes(#"Transposed Table", {{"Column3", type text},{"Column4", type text}}, "en-US"), {"Column3", "Column4"}, Combiner.CombineTextByDelimiter(" ", QuoteStyle.None), "Merged")

company name	year	month	total sales
Antonio Moreno	1997	April	456
Antonio Moreno Antonio Moreno	1997 1997	Sept Sept	521 541
Around the Horn,Around the Horn,Around the Horn	1997 1997 1997	Jan Jan Jan	410 132 245
Around the Horn	1997	May	231

Assuming that I remove the 2nd column ('region'), What I need is to get the following result:

company name	year	month	total sales
Antonio Moreno	1997	April	456
Antonio Moreno	1997	Sept	1062
Around the Horn	1997	Jan	787
Around the Horn	1997	May	231

If I could get a help over this problem that'd be really great. thanks!

Solution

The correct answer is sale of 14989.8
You have supplied the wrong data-set to obtain this answer.

Given the zipped file you linked to:

Open the 1_1_unpivot_transpose.pbix file with Power BI
This will open two Tables:

Home => Transform Data will open the Power Query Editor
Remove the top row (only has the years information)
Select Column 1 and Unpivot other columns
Group by Column 1 and Attribute
Aggregate by Sum of Value
The above produces a table like this:
At this point you have several options. Since you want to show just the single value, I suggest:
Close and Apply
Select a Card visual
Select the Total Sales/Month for the data value
Select Maximum for the aggregation (drop down next to the Data field in the setup for the visual)
Format the value to show the full number.