cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
Anonymous
Not applicable

Power Bi not recognizing same text in a column

Hello everyone,

 

So im having this problem:

 

I have a column with the status of different projects: i.e In progress, Completed, etc:

Power bi status.JPG

But, I want to replace some status text for other text, for example I want to replace "CERRADO" (spanish word) to "In progress", so what im doing is using the replace values tool in the edit query, and changin one text to the other. The actual problem is that when I do that, I end up having two exact text but power Bi is counting as if they were different:

Power bi status 2.JPG

So if I try to make a chart based on that data, im going to have duplicate information for the different categories of the status of the project. For example im having one bar of "Completed" with a number of 10 projects and then another bar in the same chart with the same label of "Completed" with 20 projects. What I want its to only have 1 bar with a number of 30 projects.

 

I already try to check if maybe I was putting a blank space when doing the replace values but everything is ok.

 

I hope you can help me. Thankyou very much!

 

 

1 REPLY 1
edhans
Super User
Super User

Before you do the Remove Duplicates step, try two transformations:

  • Right-click on the column and do Transform, Trim. This will remove any leading and trailing spaces. Visually to you they look identical, but they aren't. Then do the Remove Duplicates.
  • If they are still showing up twice, remove the Remove Duplicates step, then right-click on the column and do Transform, Clean. This will remove any special non-printing characters, like a carraige return or the Char(160) space.

If that still doesn't work, in the PQ window, click on each value you think should be the same and look in the window below. You can even select that data and put it in something like NotePad++ and try and figure out what else is in there.

 

For example:

No extra spacesNo extra spaces4 trailing spaces4 trailing spaces

In the table, these look the same, but when I click on each and highlight below, I can see there are trailing spaces on the 2nd entry, and PQ will not remove that as a duplicate until you TRIM it.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors