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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
adoalan
Helper III
Helper III

Help with formula

Hi All,

 

I need some help in removing -UK from both columns Source Code and Allocation

and also everything was is after / in the Source Code

 

adoalan_0-1680603330732.png

 

Any help please?

 

Thanks

4 REPLIES 4
DOLEARY85
Super User
Super User

Hi,

 

you can do this in power query, just right click the columns, select replace values type -uk in the first and leave the second blank

@DOLEARY85  I can't, wont let me if I'm querrieng the data from the database. To use Power Query I need to import the data and will take ages.

 

 

Ah okay, 

 

for the -uk part you could create a column in report view:

 

Column = SUBSTITUTE('Table (2)'[Column1],"-uk","")

For the part after the /  you could use the search function to count the starting character using wildcards in the search e.g 

SEARCH("???-uk",'Table (2)'[Source Code], 1, 0)
 
then use a Left function to get the left value of the string up to 1 character before the start:
 
full column: 
left('Table (2)'[Source Code],SEARCH("???-uk",'Table (2)'[Source Code], 1, 0)-1 )

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.