Exporting a Powershell object containing multiple arrays to CSV/Power BI-CodePudding

It's Friday and my brain's just not working! I have a Powershell object which is the result of DSInternals Test-PasswordQuality cmdlet that I'd like to use as a Power BI dataset; it's a bunch of arrays of user accounts, ex.

ClearTextPassword          : {}
LMHash                     : {someuser, another user...}
EmptyPassword              : {someuser, another user}
WeakPassword               : {someuser, another user...}
DefaultComputerPassword    : {etc...}
PasswordNotRequired        : {etc...}
PasswordNeverExpires       : {etc...}
AESKeysMissing             : {etc...}
PreAuthNotRequired         : {}
DESEncryptionOnly          : {}
Kerberoastable             : {}
DelegatableAdmins          : {}
SmartCardUsersWithPassword : {}
DuplicatePasswordGroups    : {}

I'd like to export this to one csv where each array is its own column. If I simply do

$result | export-csv .\result.csv -NoTypeInformation

I do get a column for each array, but then the users in each column are all combined into one cell.

example of first few columns

How can I get the same, but with each user in it's own cell?

OR, how can I get this PS object directly into a Power BI dataset with maybe the MicrosoftPowerBIMgmt module?

Thanks a million y'all!

CodePudding user response：

The object in this answer is a minimal representation of yours and is meant for the sake of demonstrating the method you can use.

First, you need to calculate the maximum count of elements of all properties, for this, we can access the PSObject.Properties and for each value, get it's count. Then, we can use a for loop to reconstruct it.

$object = [pscustomobject]@{
    ClearTextPassword       = @()
    EmptyPassword           = @('someuser', 'another user', 'yet another')
    WeakPassword            = @('someuser', 'another user')
    DefaultComputerPassword = @('etc')
    PasswordNotRequired     = @(1,2,3,4,5)
}

$max = [System.Linq.Enumerable]::Max($object.PSObject.Properties.ForEach({ $_.Value.Count }))

$result = for($i = 0; $i -lt $max; $i  ) {
    $outObject = [ordered]@{}
    foreach($prop in $object.PSObject.Properties) {
        $outObject[$prop.Name] = $prop.Value[$i]
    }
    [pscustomobject]$outObject
}

$result | Format-Table

Example above would result in the following object[], which, should be perfectly fine for export to CSV.

ClearTextPassword EmptyPassword WeakPassword DefaultComputerPassword PasswordNotRequired
----------------- ------------- ------------ ----------------------- -------------------
                  someuser      someuser     etc                                       1
                  another user  another user                                           2
                  yet another                                                          3
                                                                                       4
                                                                                       5