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

[Sathyabalan]

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

[Smalfly]

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]

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