concatenates in Power BI

[Alma1992]

Hi. I have got this formula that work in excel I want to do it in power BI. How can I do it, can you please advise? Thanks in advance!

=IF(LEFT(H2, LEN(MID(G2, 6, LEN(G2)-6))) = MID(G2, 6, LEN(G2)-6), "+" & MID(G2, 6, LEN(G2)-6) & MID(H2, LEN(MID(G2, 6, LEN(G2)-6))+1, LEN(H2)), "+" & MID(G2, 6, LEN(G2)-6) & H2)

[Daoud_H · Accepted solution]

Hi @Alma1992,

You can create a DAX measure like this : 

IF(
    LEFT([H2], LEN(MID([G2], 6, LEN([G2])-6))) = MID([G2], 6, LEN([G2])-6),
    "+" & MID([G2], 6, LEN([G2])-6) & MID([H2], LEN(MID([G2], 6, LEN([G2])-6))+1, LEN([H2])),
    "+" & MID([G2], 6, LEN([G2])-6) & [H2]
)

[Anonymous · Accepted solution]

Hi @Alma1992 

Based on your description, you want to extract the number .

In power query you can add a custom column, and input the following code.

Text.Select([WebFrom.Country Code],{"+","1".."9"})&Text.From([WebFrom.Phone])

Output

Herer is the M code in advanced editor, you can refer it.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc5NCgIxDAXgq0hXirOY/DXpVhAVN4K4Gnr/a/jSoYhvlTZfm2xbUWevRGUpn/fheKYTKmJRqx4tSl92gjMatwuIahp38hBCpgk8Q+NxhRGTROFr48zPiP2N4pW0iqpOkbNx/8pfbJDWxKohk7CMZe5PkDAehqoGUnr/Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [BookId = _t, #"WebFrom.Country Code" = _t, WebFrom.Phone = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"BookId", Int64.Type}, {"WebFrom.Country Code", type text}, {"WebFrom.Phone", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Select([WebFrom.Country Code],{"+","1".."9"})&Text.From([WebFrom.Phone]))
in
    #"Added Custom"

Best Regards!

Yolo Zhu

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

[Daoud_H · Accepted solution]

Hi @Alma1992,

You can create a DAX measure like this : 

IF(
    LEFT([H2], LEN(MID([G2], 6, LEN([G2])-6))) = MID([G2], 6, LEN([G2])-6),
    "+" & MID([G2], 6, LEN([G2])-6) & MID([H2], LEN(MID([G2], 6, LEN([G2])-6))+1, LEN([H2])),
    "+" & MID([G2], 6, LEN([G2])-6) & [H2]
)