Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
shocktech
New Member

Match and Extract String from List using Power Query

I am trying to extract the Primary SMTP address fomr the proxyAddress attibute in ADDS. When expanded the proxyAddress attribute is a list. An Exmple of the onctent might be as follows:

 

smtp: joe.bloggs@my.com
SMTP: joe.bloggs@my.com
X500:/o=VHI/ou=First Administrative Group/cn=Recipients/cn=bloggs_j
x500:/o=ExchangeLabs/ou=Exchange Administrative Group (FYDIBOHF23SPDLT)/cn=Recipients/cn=744b0ca795fa4892a71b247bcd3009f3-bloggs_j

 

The primary SMTP address is the one that begins with uppercase SMTP. As such, I need to match that list item and return it as a sring. I can't seem to figure this out using the List.Select type funtions. Any assitance would be greatly appricated. 

 

2 ACCEPTED SOLUTIONS
AlB
Super User
Super User

Hi @shocktech 

Do you mean each row in the text you show is an element in a list? And by list you refer to a PQ list, for example {1, 2, 3, 4} ?

 

SU18_powerbi_badge

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

View solution in original post

AlB
Super User
Super User

@shocktech 

Place the following M code in a blank query to see the steps of a potential solution:

let
    Source = { "smtp: joe.bloggs@my.com",
        "SMTP: joe.bloggs@my.com",
        "X500:/o=VHI/ou=First Administrative Group/cn=Recipients/cn=bloggs_j",
        "x500:/o=ExchangeLabs/ou=Exchange Administrative Group (FYDIBOHF23SPDLT)/cn=Recipients/cn=744b0ca795fa4892a71b247bcd3009f3-bloggs_j"},

    findSMTP_ = List.Select(Source, each Text.Contains(_, "SMTP")),
    res_ = Text.Combine(List.Transform(findSMTP_, each Text.Trim(Text.AfterDelimiter(_, "SMTP:"))), ", ")  
in
    res_

 

SU18_powerbi_badge

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

View solution in original post

3 REPLIES 3
AlB
Super User
Super User

@shocktech 

Place the following M code in a blank query to see the steps of a potential solution:

let
    Source = { "smtp: joe.bloggs@my.com",
        "SMTP: joe.bloggs@my.com",
        "X500:/o=VHI/ou=First Administrative Group/cn=Recipients/cn=bloggs_j",
        "x500:/o=ExchangeLabs/ou=Exchange Administrative Group (FYDIBOHF23SPDLT)/cn=Recipients/cn=744b0ca795fa4892a71b247bcd3009f3-bloggs_j"},

    findSMTP_ = List.Select(Source, each Text.Contains(_, "SMTP")),
    res_ = Text.Combine(List.Transform(findSMTP_, each Text.Trim(Text.AfterDelimiter(_, "SMTP:"))), ", ")  
in
    res_

 

SU18_powerbi_badge

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

AlB
Super User
Super User

Hi @shocktech 

Do you mean each row in the text you show is an element in a list? And by list you refer to a PQ list, for example {1, 2, 3, 4} ?

 

SU18_powerbi_badge

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

Yes indeed. I am using PowerQuery in Excel 365 Desktop app. I should have clarified this. Apologies. 

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors