O365 Powershell – Get all SMTP addresses

$cred = Get-Credential

New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://ps.outlook.com/powershell/  -Credential $cred -Authentication Basic -AllowRedirection

Import-PSSession $Session -AllowClobber

Connect-MsolService -Credential $cred

$data = Get-Recipient -Resultsize unlimited -RecipientType Usermailbox | select DisplayName, EmailAddresses 

$addrs = foreach($rec in $data){
    foreach($s in $rec.EmailAddresses -split(' ')) {
        foreach($addr in $s -split(":")) {
            if(($addr -notmatch "SPO_") 
                -and ($addr -notmatch "onmicrosoft") 
                -and ($addr -notmatch "SMTP") 
                -and ($addr -notmatch "SIP") 
                -and ($addr -notmatch "smtp") 
                -and ($addr -ne "SPO")) { 
            $rec.DisplayName + "|" + $addr }
        }
     }
}
Advertisements

Power Query – Fill Down Column

let
SourceTableName = "Change this to the name of your table"
GroupColumnName = "The header for your group column goes here"
FillColumnName = "The header for the column of values you want to fill"

Source = Excel.CurrentWorkbook(){[Name=SourceTableName]}[Content],
ColNames = Table.ColumnNames(Source),
SortByEmp = Table.Sort(Source, {{GroupColumnName, Order.Ascending}}),
GroupCols = Table.Group(SortByEmp, {GroupColumnName}, {{"GroupCol", each Table.FillDown(Table.Sort(_, {{FillColumnName ,Order.Descending}}),{FillColumnName }), type table}}),
RemCol = Table.RemoveColumns(GroupCols, GroupColumnName),
ExpTable = Table.ExpandTableColumn(RemCol, "GroupCol", ColNames)

in
ExpTable

Power Query – Unique Word List and Count from TXT File

let
numberlists = {{0..31},{33..47},{58..64},{91..96},{123..191}},
combinedlist = List.Combine(numberlists),
punctuationlist = List.Transform(combinedlist, each Character.FromNumber(_)),
Source = File.Contents(“C:\Temp\dickens-david-626.txt”),
CSVSplit = List.Combine(List.Transform(Table.ToList(Csv.Document(Source,1,”#(#)(lf)”,null,1252)), each Text.Split(_, ” “))),
RemovePunctuation = List.Transform(CSVSplit, each Text.Remove(_, punctuationlist)),
TextLower = List.Transform(RemovePunctuation, each Text.Lower(_)),
CreateTable = Table.FromList(TextLower),
FilterBlanks = Table.SelectRows(CreateTable, each ([Column1] “”)),
CountOccurences = Table.Group(FilterBlanks, {“Column1”}, {{“Count”, each Table.RowCount(_), type number}}),
RenameColumn = Table.RenameColumns(CountOccurences, {{“Column1”, “Words”}}),
SortDescending = Table.Sort(RenameColumn,{{“Count”, Order.Descending}})
in
SortDescending