Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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)
Solved! Go to 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]
)
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.
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.
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]
)
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
39 | |
21 | |
20 | |
13 | |
13 |