Use PowerShell to extract Excel Worksheey Schema and Data to create Power BI DataSet

ExcelSchemaAndData_to_PowerBI_DataSet.ps1

<# Literally one of the oldest SQL PowerShell examples, and it now works on PSCore. 
    I'm only doing this step to generate data to place in an Excel spreadsheet.
    IMPORTANT: If you already have an Excel spreadsheet, skip this section!  #>
ls 'SQLSERVER:\SQLRegistration\Database Engine Server Group' -Recurse |            
WHERE {$_.Mode -ne 'd' } |            
    foreach {            
    Invoke-Sqlcmd -ServerInstance $_.Name -Database master -OutputAs DataTables -Query "
    SELECT @@ServerName AS 'ServerName',
           DB_NAME(dbid) AS 'DatabaseName',
           name AS 'LogicalName',
           GETDATE() AS 'CheckDate',
           CONVERT(BIGINT, size) * 8 AS 'SizeInKB',
           filename AS 'DBFileName',
           SYSDATETIMEOFFSET() 'DiscoveryOccured'
      FROM master..sysaltfiles
     WHERE dbid != 32767" |            
    Write-SqlTableData -ServerInstance Localhost\SQL2017 -DatabaseName BlankDB -SchemaName dbo -TableName DatabaseSizeHistory -Force;            
    }

<# This code build the Excel file.
    Obviously, you can modify the Power BI section
    to pull the data directly from the DatabaseSizeHistory table. #>
Read-SqlTableData -ServerInstance Localhost\SQL2017 -DatabaseName BlankDB -SchemaName dbo -TableName DatabaseSizeHistory |
Export-Excel -Path c:\temp\dbSizes.xlsx -WorksheetName dbSizeHistory -Show

<#  ##############################################################################
    OK, this is the Power BI portion.
    You may not even need the InferData.ps1, but it's in the ImportExcel module.
    ############################################################################## #>
. .\InferData.ps1

$result = Import-Excel .\dbSizes.xlsx -WorksheetName 'dbSizeHistory' 

$names = $result[0].psobject.properties.name

<# This code will generate the statement below which  #>
$variables = @()
$Columns = $(for ($idx = 0; $idx -lt 1; $idx++) {    
    $colVar=0
    foreach($name in $names) {
        $variables += "`$col{0}" -f ($colVar++)
        "{0} = New-PowerBIColumn -Name {1} -DataType {2}`r`n" -f $variables[-1],$name, (Invoke-AllTests $result[$idx].$name -OnlyPassing -FirstOne).DataType
    }    
})

@"
$Columns 
`$table1 = New-PowerBITable -Name dbFileSizeHistory -Columns $($variables -join ", ")
"@

<# This code is all generated for you by the code above, just execute it.
    Don not edit the code below, it's just here for illustrative purposes #>
#   $col0 = New-PowerBIColumn -Name ServerName -DataType string
#   $col1 = New-PowerBIColumn -Name DatabaseName -DataType string
#   $col2 = New-PowerBIColumn -Name LogicalName -DataType string
#   $col3 = New-PowerBIColumn -Name CheckDate -DataType datetime
#   $col4 = New-PowerBIColumn -Name SizeInKB -DataType int
#   $col5 = New-PowerBIColumn -Name DBFileName -DataType string
#   $col6 = New-PowerBIColumn -Name DiscoveryOccured -DataType datetime
#   $col7 = New-PowerBIColumn -Name RowError -DataType string
#   $col8 = New-PowerBIColumn -Name RowState -DataType string
#   $col9 = New-PowerBIColumn -Name Table -DataType string
#   $col10 = New-PowerBIColumn -Name ItemArray -DataType string
#   $col11 = New-PowerBIColumn -Name HasErrors -DataType bool

#   $table1 = New-PowerBITable -Name dbFileSizeHistory -Columns $col0, $col1, $col2, $col3, $col4, $col5, $col6, $col7, $col8, $col9, $col10, $col11
<# /This code is all generated for you by the code above, just execute it. #>

$dataset = New-PowerBIDataSet -Name DougFinke_IsAWESOME! -Tables $table1
Login-PowerBIServiceAccount -Environment Public -Credential (Get-Credential [email protected])
Add-PowerBIDataSet -DataSet $dataset

<# As of this writing, Add-PowerBIRow only accepts data via .CSV >#> 
Import-Excel .\dbSizes.xlsx -WorksheetName 'dbSizeHistory' |Export-Csv .\data.csv -NoTypeInformation

Add-PowerBIRow -DataSetId f92973ba-b05c-4122-9deb-c339b27fbb6a -TableName SampleTable1 -Rows (Import-Csv -Path ".\data.csv")
0