Remove Duplicates Preserving Values

[migueljoa]

I would like to modify a table in Powerquery (source is a website) such that there are no duplicates but without deleting any data. Any Idea?

Example Table:

a      10

b      20

a      30

b      20

What I'd like:

a1    10

b      20

a2    30

b      20

Note: "a" is the only duplicate that I have in the table. Later I'll group by letter and sum values.

Final Desired Result

a1    10

a2    30

b      40

[Greg_Deckler]

Perhaps create a column that concatenates the two columns as text. Then you could remove your first column, group on that new column and sum the 2nd column.

You would end up with:

a10, 10

a30, 30

b20, 40

[migueljoa]

This could be a valid solution if I'd to work with only one table. However, my main goal is to create a function, use it on multiple tables from diferent sources, and then group all using sum.

To be more specific:

• The dataset is a balance sheet (Column A: assets, liability, capital, Column B: Year 20XX ) 
• I appended the same table (exact same format) from different companies multiple times
• Then Grouped by Column A, and sum Column B to get Consolidated data for the Industry.

The problem is that there is one line in Column A, that repeats 2 times (as an asset and as a liability), making the consolidation wrong. Here's a graphical example of my problem:

Note: 

A =Asset

AL = the duplicated row in Assets and Liabilities

L = Liability

C = Capital

In the example, Total Assets = 15 and Total Liabilities and Capital = 15 for each company