Forum Discussion

MichaelBauld's avatar
MichaelBauld
Frequent Visitor
4 years ago
Solved

Extracting the first letter after a defined text string within a column

Hi all, 

 

I have a dashboard showing invoice payments by supplier name. This is filtered by the first letter of each supplier account as processing invoices is assigned to staff  by the first letter of the supplier name (i.e. staff member 1 has invoices from suppliers starting with 'E' and 'F'). To pull the first letter I initially just used a calculated column alongside the 'supplier name' column to pull the first letter from the left of the supplier name:

 

=LEFT('Pay run data'[Supplier name],1))

 

However, it appears that some supplier names have a trading name and an invoicing name. These supplier accounts usually list the name in the following format:

 

EXAMPLE: 'John Scott Joinery t/a Scott Joinery' (so should be logged a 'S' not 'J')

 

So my challenge is, for suppliers where this is relevant, how can I pull the first letter after 't/a' in the name (or the first letter of the whole text string if 't/a' is not present)? Any advice would be gratefully recieved!

 

Thanks

Michael

 

Need Help

3 Replies

    • MichaelBauld's avatar
      MichaelBauld
      Frequent Visitor
      4 years ago

      Thanks amitchandak , using this variable and combined with the IF and MID functions I've the column pulling the correct letters. 

  • ddpl's avatar
    ddpl
    Icon for Solution Sage rankSolution Sage
    4 years ago

    MichaelBauld ,

    Try below steps in Power Query...

     

    Step 1: Replace "t/a " (t/a[space]) with ","

     

    Step 2: Add a Custom Column...

    Your Need = if Text.Contains( [Suppliers], "," ) = true 
                then Text.Start( Text.AfterDelimiter( [Suppliers], "," ), 1 ) 
                else Text.Start( [Suppliers], 1 )

     

    Hope it will work for you.