Populate user office location for Power BI

There is a need to populate user office location for Power BI. Here is one of the quickest way to get this information trough AD Powershell.

I've quickly developed a script to have user office populate in a csv file and upload to SharePoint for Power BI to consume. Here is the script based on AD Powershell.


$ADUsers =  Get-ADUser -Filter *  -Properties physicalDeliveryOfficeName  | where physicalDeliveryOfficeName  -ne $null
$outputFile = "C:\Harry\Projects\SPCOE\Scripts\GetADUsers\Output\ADUsers.csv"

$rows = @()

foreach($ADUser in $ADUsers)
{
    $rows += New-Object -TypeName PSObject -Property @{                                                                                                                                   

                                            Name = $ADUser.Name
                                            UPN = $ADUser.UserPrincipalName 
                                            Office = $ADUser.physicalDeliveryOfficeName                                       

                                            } | Select-Object Name,UPN,Office

}

$rows | Export-Csv $outputFile  -NoTypeInformation -Force  -ErrorAction SilentlyContinue