Forum Discussion
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: [email protected]
SMTP: [email protected]
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.
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} ?
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.
Place the following M code in a blank query to see the steps of a potential solution:
let Source = { "smtp: [email protected]", "SMTP: [email protected]", "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_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.
3 Replies
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} ?
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.
Place the following M code in a blank query to see the steps of a potential solution:
let Source = { "smtp: [email protected]", "SMTP: [email protected]", "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_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.
