Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
amirghaderi
Helper IV
Helper IV

Remove unnecessary character from data

Hi,

In the below example, how can I romove "A" and "*" when my data get loaded to power BI.

Basically, I want my data to be loaded as Date type to Power Bi. But, some records have additional character as "A" and "*".

 

amirghaderi_0-1639293006603.png

 

1 ACCEPTED SOLUTION

Hi @amirghaderi ,

 

My mistake.

Actually you could directly use Replace Values feature in Query Editor.

Capture.PNG

1.PNG2.PNG

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

5 REPLIES 5
v-jayw-msft
Community Support
Community Support

Hi @amirghaderi ,

 

You could use @themistoklis 's method if the unnecessary character always appears at the end of column.

If it will appear in other places, you can try using the TEXT.REMOVE() function in Query Editor to create a new column and then change the type of new column to date.

Text.Remove([date],{"A","*"})

 

1.PNG2.PNG

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

Thanks,

In formula, if I have as below, it will remove A from any month which has A, like April,

 

= Table.AddColumn(#"Promoted Headers", "Custom", each Text.Remove([Date],"A"))

 

amirghaderi_0-1639665090191.png

 

 

If I put one space before A, then the formula, gives an error.

amirghaderi_1-1639665147853.png

 

 

 

Hi @amirghaderi ,

 

My mistake.

Actually you could directly use Replace Values feature in Query Editor.

Capture.PNG

1.PNG2.PNG

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
themistoklis
Community Champion
Community Champion

@amirghaderi 

 

Based on the sample data you sent to us.. you can try the following formula:

 

Date_New = DATEVALUE(LEFT(Sheet1[Date],10))
bcdobbs
Super User
Super User

In Power Query click the column and then add column from example. (There's a drop down to do it based on selection). Just type in a few examples of what you want. If month is always 3 characters it will generate something simple.

 

Once done set your new column as date type, delete old one and rename.



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.