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)    ...
Need Help
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