Need help to data Clean

[tejapowerbi123]

Hello Community,

Can you please review below and provide your suggestion? I am not sure how to deal with below requirements.

Thanks

RAW DATA
ID	Province	Budget
A	Ontario
A		2000
A
B
B	Nova Scotia
B
B		1500
C	Alberta	2000

Final Result
ID	Province	Budget
A	Ontario	2000
B	Nova Scotia	1500
C	Alberta	2000

[pranit828]

HI @tejapowerbi123 

If you need the result in a visual.

Create two measures

_province = MAX([province]
_budget = MAX([budget]

and ID in the columns section of metrix

annd above created function in values.

[nandic · Accepted solution]

@tejapowerbi123 ,

Try using Group By option inside Power Query.
Select column ID > choose Group by option > add these rules:

[GilbertQ]

Hi there

What you can do is in Power Query to filter out rows they are blank.

Then when you bring this data into your dataset, it will automatically filter out blank values.

When you put this into a table it will display your final result.