Power Query to summarise Data

[richy89]

Hi all,

I would like to summarise text data using power query but have struggled to find a solution.

I would like power query to count values in one column and then return a "multiple" or "missing" for blank cells.

See attached for details. I have a raw data tab and what I am hoping to get in the "Finished" tab.

Thanks!

[sandy666]

try

Please Login or Register  to view this content.

ID	Personel name	company	Code in Doc 1	Code in Doc 2	Correct Code? = Apply Code in Doc 1
880052042	John	Motors 3	XYZ100	XYZ100	XYZ100
880052042	John	Motors 3	XYZ100	XYZ200
880052042	John	Motors 3	XYZ100	XYZ300
880052042	John	Motors 3	XYZ100	XYZ400
880052042	John	Motors 3	XYZ100	XYZ500
880051022	Sarah	Motors 3	XYZ300	XYZ300	XYZ300
880051651	Smith	Motors 6	XYZ300	XYZ100
880051089	Richard	Motors 8	XYZ500

[richy89]

consolidate.PNG

Thanks, but the solution hasn't worked.

See the image. The codes highlighted in red are not the same so I want power query in this instance to mark this as "multiple" and then consolidate all row into one row.

[sandy666]

post excel file with source data AND EXPECTED result

[richy89]

See first post. Excel file attached

[sandy666]

You mean you want words in the table?

[richy89]

I basically want to get from here

start.PNG


to here;

Finish.PNG


Excel file attached

Thanks

[sandy666]

ID	Personel name	company	Code in Doc 1	Code in Doc 2
880052042	John	Motors 3	XYZ100	Multiple
880051022	Sarah	Motors 3	XYZ300	XYZ300
880051651	Smith	Motors 6	XYZ300	XYZ100
880051089	Richard	Motors 8	XYZ500	Missing

[richy89]

Thanks Sandy.

This is exactly what I wanted

[sandy666]

You are welcome

---

Click the Add Reputation at the foot of any of the posts of members who helped you