Exclude Specific Columns

[ZebulaCodes]

Is there a way to exclude columns like "RowError", "RowState", "Table", "ItemArray", "HasErrors", etc?

I am using this in conjunction with a SQL query and I only want to show the columns from the query itself.

[tomerikko]

Not 100% sure what you do exactly,
But if you save your query to an array , you can then filter it using pipe & Select-Object function.

[ZebulaCodes]

I'm just wanting to run a SQL query to return some columns and rows using New-HTMLTable to build a report. Problem is if I only want 4 or 5 columns, it will populate the rest with things like "RowError", "RowState", etc. For the sake of readability, I don't want to send my users a report with columns that do not pertain to the data from the SQL query.

[PrzemyslawKlys]

In the new version that I will release today or tomorrow you can do:

New-HTML {
    New-HTMLTable -DataTable $DataTable1 -ExcludeProperty "RowError", "RowState", "Table", "ItemArray", "HasErrors"
} -ShowHTML -FilePath $PSScriptRoot\Example-TableJavaScriptConditions.html -Online

You can also specify it the other way around

$DataTable1 = @(
    [PscustomObject] @{ DateTest = '2027-09-12'; DateUS = '3/31/2020'; Dates = (Get-Date).AddDays(2); BoolAsString = 'true'; BoolTest = $true; Test = 'ABC', 'DEF'; Test2 = 'Name1'; Test3 = 'Name3'; 'Test4' = 1 }
    [PscustomObject] @{ DateTest = '2021-01-12'; DateUS = '3/23/2020'; Dates = (Get-Date).AddDays(0); BoolAsString = 'false'; BoolTest = $false; Test = 'Opps'; Test2 = 'Name2'; Test3 = 'Name2'; 'Test4' = 2 }
    [PscustomObject] @{ DateTest = '1982-08-15'; DateUS = '3/5/2020'; Dates = (Get-Date).AddDays(-7); BoolAsString = 'false'; BoolTest = $null; Test = 'Oh No'; Test2 = 'Name3'; Test3 = 'KitKat'; 'Test4' = 3 }
    [PscustomObject] @{ DateTest = '2021-03-12'; DateUS = '4/5/2020'; Dates = (Get-Date).AddDays(13); BoolAsString = 'null'; BoolTest = $true; Test = 'Name'; Test2 = 'Name4'; Test3 = 'Name3'; 'Test4' = 0 }
    [PscustomObject] @{ DateTest = '2021-03-12'; DateUS = '3/15/2020'; Dates = (Get-Date).AddDays(5); BoolAsString = 'true'; BoolTest = $false; Test = 'Name'; Test2 = 'Name5'; Test3 = 'Name4'; 'Test4' = $null }
    [PscustomObject] @{ DateTest = '2025-01-17'; DateUS = '3/5/2020'; Dates = (Get-Date).AddDays(0); BoolAsString = 'True'; BoolTest = $false; Test = 'Name'; Test2 = 'Name2'; Test3 = 'KitKat'; 'Test4' = 10 }
    [PscustomObject] @{ DateTest = '2021-03-12'; DateUS = '7/5/2020'; Dates = (Get-Date).AddDays(21); BoolAsString = 'true'; BoolTest = $true; Test = 'Name'; Test2 = 'Name2'; Test3 = 'Bounty'; 'Test4' = 5 }
    [PscustomObject] @{ DateTest = '2021-12-12'; DateUS = '12/5/2021'; Dates = (Get-Date).AddDays(5); BoolAsString = 'True'; BoolTest = $true; Test = 'Name'; Test2 = 'Name2'; Test3 = 'Test'; 'Test4' = 0 }
)

New-HTML {
    New-HTMLTable -DataTable $DataTable1 -IncludeProperty 'DateTest', 'DateUs'
} -ShowHTML -FilePath $PSScriptRoot\Example-TableJavaScriptConditions.html -Online

There is also a version available already:

$DataTable1 = @(
    [PscustomObject] @{ DateTest = '2027-09-12'; DateUS = '3/31/2020'; Dates = (Get-Date).AddDays(2); BoolAsString = 'true'; BoolTest = $true; Test = 'ABC', 'DEF'; Test2 = 'Name1'; Test3 = 'Name3'; 'Test4' = 1 }
    [PscustomObject] @{ DateTest = '2021-01-12'; DateUS = '3/23/2020'; Dates = (Get-Date).AddDays(0); BoolAsString = 'false'; BoolTest = $false; Test = 'Opps'; Test2 = 'Name2'; Test3 = 'Name2'; 'Test4' = 2 }
    [PscustomObject] @{ DateTest = '1982-08-15'; DateUS = '3/5/2020'; Dates = (Get-Date).AddDays(-7); BoolAsString = 'false'; BoolTest = $null; Test = 'Oh No'; Test2 = 'Name3'; Test3 = 'KitKat'; 'Test4' = 3 }
    [PscustomObject] @{ DateTest = '2021-03-12'; DateUS = '4/5/2020'; Dates = (Get-Date).AddDays(13); BoolAsString = 'null'; BoolTest = $true; Test = 'Name'; Test2 = 'Name4'; Test3 = 'Name3'; 'Test4' = 0 }
    [PscustomObject] @{ DateTest = '2021-03-12'; DateUS = '3/15/2020'; Dates = (Get-Date).AddDays(5); BoolAsString = 'true'; BoolTest = $false; Test = 'Name'; Test2 = 'Name5'; Test3 = 'Name4'; 'Test4' = $null }
    [PscustomObject] @{ DateTest = '2025-01-17'; DateUS = '3/5/2020'; Dates = (Get-Date).AddDays(0); BoolAsString = 'True'; BoolTest = $false; Test = 'Name'; Test2 = 'Name2'; Test3 = 'KitKat'; 'Test4' = 10 }
    [PscustomObject] @{ DateTest = '2021-03-12'; DateUS = '7/5/2020'; Dates = (Get-Date).AddDays(21); BoolAsString = 'true'; BoolTest = $true; Test = 'Name'; Test2 = 'Name2'; Test3 = 'Bounty'; 'Test4' = 5 }
    [PscustomObject] @{ DateTest = '2021-12-12'; DateUS = '12/5/2021'; Dates = (Get-Date).AddDays(5); BoolAsString = 'True'; BoolTest = $true; Test = 'Name'; Test2 = 'Name2'; Test3 = 'Test'; 'Test4' = 0 }
)

New-HTML {
    New-HTMLTable -DataTable $DataTable1 {
        New-TableColumnOption -ColumnIndex 0, 1, 2 -Hidden $true
    }
} -ShowHTML -FilePath $PSScriptRoot\Example-IncludeExcludeProperties3.html -Online

The difference between IncludeProperty/ExcludeProperty that I am adding is what @tomerikko mentioned, just I do it for you inside New-HTMLTable. The other version that is already available uses DataTables functionality and it hides the columns while displaying it ... but the data is there.

[ZebulaCodes]

EDIT: Fixed my script to have -ColumnIndex filtering work.

I see. Thank you for the reply!

Also, not to tack on extra questions, will there be (is there) a way to include hyperlinks based on the row/cell? Say I create a table with a ticket# and a description of the ticket, could I make the ticket# a hyperlink to the ticket itself?

Ticket: Description:

INC058 Broken monitor.

[PrzemyslawKlys]

Yes. The clue here is using InvokeHTLMTags for New-HTMLTable and then you would need to make sure that your objects contain proper HTML as part of your property you want to activate within. Here's an example with QR codes within table.

New-HTML -Online -FilePath $PSScriptRoot\Example-QR02.html {
    New-HTMLSection -Invisible {
        New-HTMLPanel {
            New-HTMLQRCode -Link 'https://evotec.xyz'
        }
        New-HTMLPanel {
            New-HTMLQRCode -Link 'https://evotec.xyz' -Height 250 -Width 250 -Logo 'https://evotec.xyz/wp-content/uploads/2015/05/Logo-evotec-012.png' -LogoWidth 100 -LogoInline
        }
    }
    # Unfortunetly this table when defined outside of New-HTML won't work, not yet - would need to think on new solution
    $Table = @(
        [PSCustomObject] @{
            ComputerName = 'EvoWin'
            QRCode       = New-HTMLQRCode -Link 'https://evotec.xyz/EvoWin' -Height 50 -Width 50 -Title 'Evotec' -TitleColor Red
        }
        [PSCustomObject] @{
            ComputerName = 'EvoMac'
            QRCode       = New-HTMLQRCode -Link 'https://evotec.xyz/EvoMac' -Height 50 -Width 50 -Logo 'https://evotec.xyz/wp-content/uploads/2015/05/Logo-evotec-012.png'
        }
    )
    New-HTMLTable -DataTable $Table -Filtering -InvokeHTMLTags
} -ShowHTML

Or in this example I'm showing how HTML acts with and without InvokeHTMLTags.

$Test1 = [PSCustomobject] @{
    'First column' = 'Test'
    'Second column' = '<br>MyIdol<br><strong>Test</strong>'
    'Example' = "`r`nTesting how PowerShell NewLines are treated $([Environment]::NewLine) Or how HTML tags are treated<br> Get it?"
}

New-HTML -TitleText 'My title' -Online -FilePath $PSScriptRoot\Example19.html -Verbose {
    New-HTMLSection -HeaderText 'Showing how tags are invoked. So BR and other tags like A HREF should be converted' {
        New-HTMLTable -DataTable $Test1 -PagingOptions @(50, 100, 150, 200) -Filtering -FilteringLocation Bottom -InvokeHTMLTags
    }
    New-HTMLSection -HeaderText 'Showing how PowerShell new lines such us `r`n and [Environment]::NewLine are changed into new lines in HTML' {
        New-HTMLTable -DataTable $Test1 -PagingOptions @(50, 100, 150, 200) -Filtering -FilteringLocation Bottom
    }
    New-HTMLSection -HeaderText 'Showing Same data but with Disable PowerShell NewLine' {
        New-HTMLTable -DataTable $Test1 -PagingOptions @(50, 100, 150, 200) -Filtering -FilteringLocation Bottom -DisableNewLine
    }
    New-HTMLSection -HeaderText 'Showing Same data but with Disable PowerShell NewLine but with InvokeHTMLTags' {
        New-HTMLTable -DataTable $Test1 -PagingOptions @(50, 100, 150, 200) -Filtering -FilteringLocation Bottom -DisableNewLine -InvokeHTMLTags
    }

} -ShowHTML

[PrzemyslawKlys]

Release is now live.

[ZebulaCodes]

You're amazing!