Forum Discussion
Helper IVRemove 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 "*".
Hi amirghaderi ,
My mistake.
Actually you could directly use Replace Values feature in Query Editor.
Best Regards,
Jay
5 Replies
- bcdobbs
Community Champion
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.
- themistoklis
Based on the sample data you sent to us.. you can try the following formula:
Date_New = DATEVALUE(LEFT(Sheet1[Date],10)) 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","*"})Best Regards,
Jay
amirghaderiThanks,
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"))
If I put one space before A, then the formula, gives an error.
Hi amirghaderi ,
My mistake.
Actually you could directly use Replace Values feature in Query Editor.
Best Regards,
Jay
