Power BI Service - Using MID and FIND, tried to extract 5 letters from a Device Name

[Sathyabalan]

Hi All,

Good Day!

I am trying to extract 5 letters from a Device Names Column to a new column in the Power BI Service, wherever the Device Names have letter H then from H, 5 letters should be extracted but I am getting below error while running the below DAX Function, some device names wont have letter 'H' that need to be ignored.

Code = MID(Device[Device Name],FIND("H",'Device[Device Name],1),5)

getting below error

The following syntax error occurred during parsing: Invalid token, Line 1, Offset 34,

Device Names

W-L-H0000-S

AAA-H3433-W

L-SS-0908000

AE-EEELLOE90

S-E-H00e0-dd

After Extraction the new column should be like as below

H0000

H3433

0 or null

0

H00E0

Best Regards

Sathya

[ThxAlot · Accepted solution]

DAX wasn't designed for such tasks; use PQ to achieve it.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JYwxCsAgEAT/cnUWTM4ilhYHQg4ELUwidub/X4gx2yw7DFsrFSiCGUGmtlTy3iOwZUaZW5EzjDP7UH5BICKqUdwPMuR7eAx6n+C8boR1Y8RjtkVKiVp7AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Device Names" = _t]),

    #"Device Name" = Table.AddColumn(Source, "Device Name", each let l = Text.Split([Device Names], "-"), pos = List.PositionOf(l, "h", Occurrence.First, (x,y) => Text.Length(x) = 5 and Text.StartsWith(x, y, Comparer.OrdinalIgnoreCase)) in try l{pos} otherwise null)
in
    #"Device Name"

[v-kaiyue-msft · Accepted solution]

Hi @Sathyabalan ,

Your solution is great, @ThxAlot . It worked like a charm! Here I have another idea in mind, and I would like to share it for reference. 

Create a calculated column.

NewColumn = 
VAR startPos = SEARCH("H", 'Device'[Device Names], 1, LEN('Device'[Device Names]))
VAR result = IF(startPos > 0, MID('Device'[Device Names], startPos, 5), BLANK())
RETURN result

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[v-kaiyue-msft · Accepted solution]

Hi @Sathyabalan ,

Your solution is great, @ThxAlot . It worked like a charm! Here I have another idea in mind, and I would like to share it for reference. 

Create a calculated column.

NewColumn = 
VAR startPos = SEARCH("H", 'Device'[Device Names], 1, LEN('Device'[Device Names]))
VAR result = IF(startPos > 0, MID('Device'[Device Names], startPos, 5), BLANK())
RETURN result

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[Sathyabalan]

Hi @v-kaiyue-msft 

yes, it worked like a charm but wherever the H is not there it extracts the last letter of the Device Name instead of showing 0 or null, if any workaround, would be greatly appreciated

Best Regards

Sathya

[v-kaiyue-msft · Accepted solution]

Hi @Sathyabalan ,

You can try this.

NewColumn = 
VAR startPos = SEARCH("H", 'Device'[Device Names], 1, 0)
VAR result = IF(startPos > 0, MID('Device'[Device Names], startPos, 5), "0")
RETURN result

Best Regards,

Clara Gong

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

[Sathyabalan]

Hi @v-kaiyue-msft ,

It worked like a charm! Thank You Very Much

[ThxAlot]

I'm afraid SEARCH() doesn't work when it comes to more than one occurrences of "H".

[ThxAlot · Accepted solution]

DAX wasn't designed for such tasks; use PQ to achieve it.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JYwxCsAgEAT/cnUWTM4ilhYHQg4ELUwidub/X4gx2yw7DFsrFSiCGUGmtlTy3iOwZUaZW5EzjDP7UH5BICKqUdwPMuR7eAx6n+C8boR1Y8RjtkVKiVp7AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Device Names" = _t]),

    #"Device Name" = Table.AddColumn(Source, "Device Name", each let l = Text.Split([Device Names], "-"), pos = List.PositionOf(l, "h", Occurrence.First, (x,y) => Text.Length(x) = 5 and Text.StartsWith(x, y, Comparer.OrdinalIgnoreCase)) in try l{pos} otherwise null)
in
    #"Device Name"

[Sathyabalan]

Hi @ThxAlot Thank very much for your prompt reply and trying to help, but I have little knowledge on the Power Query in Power BI Serivice, Are you saying that I need to create a measure with your query in the PQ or Additional Column or Table?, Not sure how to insert your PQ into Power Bi Service, I really appreciated your efforts to make a PQ for me, If you guide me a little then I can make it