Forum Discussion

Rnaval's avatar
Rnaval
Icon for Post Partisan rankPost Partisan
1 year ago
Solved

Need help with a formula to transform values in a column

Hi All -

 

I have an excel data file with a column where I need a formula to transform the values. See below, I basically want just the hostname without the domain or blanks if there is no value in the cell.

 

Here's what the data should look like before and after the transform.

 

Before                                                                                          After

TrainingPC.emd.nyct.com  (delimiter is the first period)             TrainingPC

OperatorPC2  (there is no delimiter)                                           OperatorPC2

Blank Value (cell is blank)                                                            Blank Value

 

Appreciate any help with this.

 

Thanks...Rohit

 

 

Need Help
  • techies's avatar
    techies
    1 year ago

    = if Text.Trim(Text.From([Hostnames])) = "" then "" else Text.Upper(Text.BeforeDelimiter(Text.From([Hostnames]), "."))

4 Replies

  • techies's avatar
    techies
    Icon for Super User rankSuper User
    1 year ago

    Hi Rnaval in the power query editor, you can add this custom column

     

    = if [Hostnames] = null or Text.Trim([Hostnames]) = "" then "" else Text.BeforeDelimiter([Hostnames], ".")

  • Rnaval's avatar
    Rnaval
    Icon for Post Partisan rankPost Partisan
    1 year ago

    Thank You...that worked quite well...

     

    One more thing, how do I make the values all uppercase?

    • techies's avatar
      techies
      Icon for Super User rankSuper User
      1 year ago

      = if Text.Trim(Text.From([Hostnames])) = "" then "" else Text.Upper(Text.BeforeDelimiter(Text.From([Hostnames]), "."))

      • v-saisrao-msft's avatar
        v-saisrao-msft
        Icon for Community Support rankCommunity Support
        1 year ago

        Hi Rnaval ,
        I wanted to check if you had the opportunity to review the information provided by techies . Please feel free to contact us if you have any further questions. If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
        Thank you.