Forum Discussion

New Member

3 years ago

Solved

Power BI DAX help

Dear Friends,

I have data set like below. i want check if the column have value Like "JIRA-XXXX. X-Can be text or number. if the text is JIRA-xxxx True else False.

Thank you for your help

Tag_Jira

ACS-103352

AMSENT

JIRA-

JIRA_ATLAS

JIRA_DCOPS

JIRA-00000

JIRA-ACREQ

JIRA-ACS

JIRA-ACSS

JIRA-ACTEAM

JIRA-ADC

JIRA-ADCS

JIRA-AE

JIRA-AEMSTREQ

JIRA-AEPCMM

JIRA-AEPCSSRE

JIRA-AEPE

JIRA-AERO

JIRA-AGENTS

JIRA-AI

JIRA-AIQ

JIRA-ALCT

JIRA-ALFD

JIRA-AMO

JIRA-AMOREQ

JIRA-AMSAUTO

JIRA-AMSBSC

JIRA-AMSCC

JIRA-AMSE

JIRA-AMSEH

adudani
3 years ago
Thanks for clarifying.

try this.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc89DsIwDAXgu2SmUqHiAMYJ0KqmITZTVVWdECv3H/iTqJ+n90mOnjOOwZb73D2eS5g2YyDWals3zX73o2i62Dd2baHqn2aynnRl5CGvrOrPrCQu6eqpAJAlEufIAL+afBY17EiZRdCqBd5kUBmcTu9v+67WZ1/Ts3kdo5MMALxPlG4GC3pQBjMyoc5hml4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Tag_Jira", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each if Text.StartsWith([Tag_Jira],"JIRA-") and Text.Length([Tag_Jira])=10 then "yes" else "no")
in
#"Added Custom"
adudani
3 years ago
if you want a calculated column in the data view,

try =
if ( LEFT('Table (3)'[Tag_Jira],5)= "JIRA-" && LEN('Table (3)'[Tag_Jira])=10, "yes","no")

5 Replies

adudani
Memorable Member
3 years ago
Hi zenbex,

You can create a calculated column in power query.

paste the code below in a blank query for my solution:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc89DsIwDAXgu2SmUqHiAMYJ0KqmITZTVVWdECv3H/iTqJ+n90mOnjOOwZb73D2eS5g2YyDWals3zX73o2i62Dd2baHqn2aynnRl5CGvrOrPrCQu6eqpAJAlEufIAL+afBY17EiZRdCqBd5kUBmcTu9v+67WZ1/Ts3kdo5MMALxPlG4GC3pQBjMyoc5hml4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Tag_Jira", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each if Text.Contains([Tag_Jira],"JIRA") then "Yes" else "No")
in
#"Added Custom"

Please let me know if this resolves the question.

Appreciate a thumbs up if this is helpful.
- zenbex
  New Member
  3 years ago
  Thank you for fast reply. but my quetion was a value the begins with "JIRA-" and five character only will be yes other wise it will be NO
  - adudani
    Memorable Member
    3 years ago
    Thanks for clarifying.
    
    try this.
    
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc89DsIwDAXgu2SmUqHiAMYJ0KqmITZTVVWdECv3H/iTqJ+n90mOnjOOwZb73D2eS5g2YyDWals3zX73o2i62Dd2baHqn2aynnRl5CGvrOrPrCQu6eqpAJAlEufIAL+afBY17EiZRdCqBd5kUBmcTu9v+67WZ1/Ts3kdo5MMALxPlG4GC3pQBjMyoc5hml4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Tag_Jira", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each if Text.StartsWith([Tag_Jira],"JIRA-") and Text.Length([Tag_Jira])=10 then "yes" else "no")
    in
    #"Added Custom"
adudani
Memorable Member
3 years ago
if you want a calculated column in the data view,

try =
if ( LEFT('Table (3)'[Tag_Jira],5)= "JIRA-" && LEN('Table (3)'[Tag_Jira])=10, "yes","no")

Related content