cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Subhakar
New Member

can we do this similar below in power BI dax.

case
when position('jdbc:snowflake' in jdbc_url) > 0 then split_part(split_part (jdbc_url, '/', 3), ':',1)
when position('jdbc:oracle' in jdbc_url) > 0 then split_part(split_part (jdbc_url, '@', 2), ':',1)
when position('jdbc:sqlserver' in jdbc_url) > 0 then split_part(split_part (jdbc_url, '/', 3), ':',1)
when position('jdbc:db2' in jdbc_url) > 0 then split_part(split_part (jdbc_url, '/', 3), ':',1)
when position('jdbc:teradata' in jdbc_url) > 0 then split_part(split_part (jdbc_url, '/', 3), ':',1)
when position('jdbc:postgresql' in jdbc_url) > 0 then split_part(split_part (jdbc_url, '/', 3), ':',1)
when position('jdbc:spark' in jdbc_url) > 0 then split_part(split_part (jdbc_url, '/', 3), ':',1)
when position('jdbc:mongodb' in jdbc_url) > 0 then split_part(split_part (jdbc_url, '=', 2), ';',1)
else jdbc_url
end as JdbcHost

 

 

Eg: jdbc:oracle:thin:@abcdefg.ddshdss.org:1111/dsdsds - need to split the particular text (abcdefg.ddshdss.org)
      jdbc:sqlserver://asasasasas.dsads2000.org:34343; - (asasasasas.dsads2000.org)

1 ACCEPTED SOLUTION

@Subhakar , Check this power query solution.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Ncs5DoAgFEXRrRhqAzhU38Z9GArg4xQikWdcv0P0nvoOg1jZeUrZ+hjomJeNeus8h3GSzJgZkClPVN0pxqMQpvw27BEhnyGTUhY/ybCMWmv9rk1764QxFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","@","//",Replacer.ReplaceText,{"Column1"}),
    #"Extracted Text Between Delimiters" = Table.TransformColumns(#"Replaced Value", {{"Column1", each Text.BetweenDelimiters(_, "//", ":"), type text}})
in
    #"Extracted Text Between Delimiters"

 

Add this code as power query/transform data in blank query and check

View solution in original post

4 REPLIES 4
v-yetao1-msft
Community Support
Community Support

Hi @Subhakar 

Compared to using DAX, it is easier to use the split function in Power Query. The method provided by @amitchandak  is feasible , and I follow his method to extract the data you want .

Ailsamsft_0-1644975641373.png

Best Regard

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

@Subhakar , you have to create something like that

 

new column =

var _1 =mid([jdbc_url],search("/", [jdbc_url],,0 ) ,3)
Switch(True() ,
search("jdbc:snowflake", [jdbc_url],,0 ) >0 , mid(_1,search(":", _1,,0 ) ,1)
// Add other columns
)

@amitchandak, This above DAX is not working for me. jdbc_url is a column not a hard corded value or measure. Could u please help the exact dax fo this solution?

 

@Subhakar , Check this power query solution.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Ncs5DoAgFEXRrRhqAzhU38Z9GArg4xQikWdcv0P0nvoOg1jZeUrZ+hjomJeNeus8h3GSzJgZkClPVN0pxqMQpvw27BEhnyGTUhY/ybCMWmv9rk1764QxFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","@","//",Replacer.ReplaceText,{"Column1"}),
    #"Extracted Text Between Delimiters" = Table.TransformColumns(#"Replaced Value", {{"Column1", each Text.BetweenDelimiters(_, "//", ":"), type text}})
in
    #"Extracted Text Between Delimiters"

 

Add this code as power query/transform data in blank query and check

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors
Top Kudoed Authors