Forum Discussion
Remove Duplicates Preserving Values
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
2 Replies
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
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
