Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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.
Solved! Go to Solution.
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: 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_
|
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: 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_
|
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. |
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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
13 | |
9 | |
8 | |
7 |
User | Count |
---|---|
17 | |
10 | |
7 | |
6 | |
6 |