Forum Discussion

Sathyabalan's avatar
Sathyabalan
Frequent Visitor
2 years ago

DAX To Extract Text between the Hyphen from a table column in Power Bi Service

Hi All,

 

I am trying to extract the text between the Hypen

 

#"Device Names" = Table.AddColumn(Source, "Device Name", each Text.BetweenDelimiters([Device Name], "-", "-"), type text)

 

 

Result should be like below wherever the text not between Hyphen then it should be 0 or null

 

 

Regards

Sathya

Need Help

2 Replies

  • Smalfly's avatar
    Smalfly
    Icon for Responsive Resident rankResponsive Resident
    2 years ago

    Hi Sathyabalan ,

     

    if you have a max occurence of 2 hyphens, you can try to first split you column on the right-most delimiter. And then split the left part based on the left-most identifier:

     

     

     

    In the advaced editor, this becomes:

     

    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Original text", "Original text - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Original text - Copy", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, true), {"Original text - Copy.1", "Original text - Copy.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Original text - Copy.1", type text}, {"Original text - Copy.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Original text - Copy.1", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, false), {"Original text - Copy.1.1", "Original text - Copy.1.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Original text - Copy.1.1", type text}, {"Original text - Copy.1.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Original text - Copy.1.2", "Result"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns",null,"0",Replacer.ReplaceValue,{"Result"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Replaced Value",{"Original text", "Result", "Original text - Copy.1.1", "Original text - Copy.2"})

     

    Good luck!

    • Sathyabalan's avatar
      Sathyabalan
      Frequent Visitor
      2 years ago

      Hi Smalfly Good Day!

      I am using Power Bi Service where this options not available in GUI and the Advanced Editor as well, so looking for a function to create an addtional column, even We found a way to it by running the below Dax Function

       

      HotelCodeAll = VAR startPos = SEARCH("H", 'Device'[Device Name], 1, 0)
      VAR result = IF(startPos > 0, MID('Device'[Device Name], startPos, 5), "0")
      RETURN result
       
      but if there are two H vaule in the device name then it extracted as H-H245, instead of H2456 if the device name is GH-H2456 that is why We thought of going with text delimiter dax function - 'Text.BetweenDelimiters' to extract between Hyphen, that will resolve the issue, I can do it in Power Bi Desktop using the sample from column example also, so looking for DAX function only to extrac between the Hyphen in Power Bi Service
       
      Best Regards
      Sathya