PowerBIPS.Tools.psm1

<#
 
Copyright (c) 2017 DevScope
 
Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:
 
The above copyright notice and this permission notice shall be included in
all copies or substantial portions of the Software.
 
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
THE SOFTWARE.
 
#>


Function Convert-PowerBIDesktopToASTabular{ 
<#
.SYNOPSIS
A quick way to convert a Power BI Desktop file into an Analysis Services Tabular Project
.DESCRIPTION
A quick way to convert a Power BI Desktop file into an Analysis Services Tabular Project
.PARAMETER pbiDesktopWindowName
Power BI Desktop window name, wildcards can be used. Ex: "*name*"
.PARAMETER pbiDesktopPbiTemplatePath
Path to PowerBI Template
.PARAMETER outputPath
Path to the output folder
.PARAMETER removeInternalPBITables
This remove internal tables like "Localdate_XXXXXX"
.EXAMPLE
Convert-PowerBIDesktopToASTabular -pbiDesktopWindowName "*VanArsdel - Sales*" -outputPath ".\SSASProj"
#>

    [CmdletBinding()] 
    param
    (
        [Parameter(Mandatory = $true, ParameterSetName = 'pbiDesktopWindowName')]
        [string]  
        $pbiDesktopWindowName
        ,
        [Parameter(Mandatory = $true, ParameterSetName = 'pbiDesktopPbiTemplatePath')]
        [string]  
        $pbiDesktopPbiTemplatePath
        ,
        [Parameter(Mandatory = $true)]
        [string]
        $outputPath
        ,
        [Parameter(Mandatory = $false)]
        [int]
        $compatibilityLevel = 1400
        ,
        [Parameter(Mandatory = $false)]
        [switch]
        $removeInternalPBITables
        ,
        [Parameter(Mandatory = $false)]        
        $modelFileName = "model.bim"
        ,
        [Parameter(Mandatory = $false)]
        [switch]
        $onlyModel
    )

    try
    { 
        if ($PSCmdlet.ParameterSetName -eq 'pbiDesktopPbiTemplatePath'){

            if(!(Test-Path  $pbiDesktopPbiTemplatePath)){
                throw "Template PBI Not found"             
            }

            $bytes = [System.IO.File]::ReadAllBytes($pbiDesktopPbiTemplatePath)

            $encoding = [System.Text.Encoding]::Unicode
                
            $modelSchema = Get-ZipSection -bytes $bytes -entryName "DataModelSchema" -encoding $encoding

            $deserializeOptions = new-object Microsoft.AnalysisServices.Tabular.DeserializeOptions                    

            $database = [Microsoft.AnalysisServices.Tabular.JsonSerializer]::DeserializeDatabase($modelSchema,$deserializeOptions)                       
        
        }else{  

            # Get the PBI window port
     
            $obj = Get-PBIDesktopTCPPort $pbiDesktopWindowName
            $port = $obj.Port

            $dataSource = "localhost:$port"
            
            $server = New-Object Microsoft.AnalysisServices.Tabular.Server

            $server.Connect($dataSource)

            $database = $server.Databases[0]

        }

        #remove internal tables

        if($removeInternalPBITables){

            $relationships = $database.Model.Relationships | where {$_.ToTable.Name -Match "LocalDateTable"}
        
            $relationships |% {
      
                $database.Model.Relationships.Remove($_.Name)
       
            }

            $LocalDate = $database.Model.Tables | where {$_.Name -Match "DateTableTemplate" -or $_.Name -Match "LocalDateTable"}

            $LocalDate |% {

               $database.Model.Tables.Remove($_.Name)  
            }
        }
        
        #each tables

        $database.Model.Tables |% {
            
            $table = $_

            if($removeInternalPBITables)
            {
                $table.Columns |% {$_.Variations.Clear()}
            }

            $table.Partitions |% {

                $partition = $_

                if ($partition.SourceType -eq "Query")
                {                
                    Write-Verbose "Converting partition $($partition.Name)"

                    $mExpression = Get-MCodeFromPBIDataSource $partition
                    
                    if ($mExpression[0].hiddenTable -eq $false )
                    {                    
                        $mExpression = $mExpression.expression
                    }
                    else
                    {                        
                       foreach($obj in $mExpression) 
                       {
                            if ($obj.name.Replace("#","").Replace('"','').Trim() -eq $table.Name.Trim()) 
                            { 
                                if($table.Name.Contains(' '))
                                {
                                    $mExpression = "let`n`tSource = #`"$($obj.name.Trim())`"`nin Source"
                                } else {
                                    $mExpression = "let`n`tSource = $($obj.name)`nin Source"
                                }
                            }  
                                                      
                            $exist = $database.Model.Expressions | Where-Object { $_.Name.Trim() -eq $obj.name.Trim() }

                            if ($exist.Count -eq 0 `
                                    -and -not($obj.name.Trim().Contains("QueryBinding")) `
                                    )
                            {
                                $ex = new-object Microsoft.AnalysisServices.Tabular.NamedExpression
                                    
                                $ex.Name = $obj.name.Trim()

                                $ex.Kind = new-object Microsoft.AnalysisServices.Tabular.ExpressionKind

                                $ex.Description = ""

                                $ex.Expression = $obj.expression

                                $database.Model.Expressions.Add($ex)
                            }                         
                        }
                       
                    }                
                    
                    $mPartitionSource = new-object Microsoft.AnalysisServices.Tabular.MPartitionSource                
                    
                    $mPartitionSource.Expression = $mExpression

                    $partition.Source = $mPartitionSource                
                }
            }

        }        
        
        $database.Model.DataSources.Clear()

        $database.CompatibilityLevel = $compatibilityLevel
        $serializeOptions = new-object Microsoft.AnalysisServices.Tabular.SerializeOptions        
        $serializeOptions.IgnoreTimestamps = $true
        $serializeOptions.IgnoreInferredProperties = $true
        $serializeOptions.IgnoreInferredObjects = $true
        
        $dbJson = [Microsoft.AnalysisServices.Tabular.JsonSerializer]::SerializeDatabase($database, $serializeOptions)

        New-Item -ItemType Directory -Path $outputPath -ErrorAction SilentlyContinue | Out-Null
       
        $dbJson | Out-File "$outputPath\$modelFileName" -Force

        if (!$onlyModel -and !(Test-Path "$outputPath\ssasproject.smproj"))
        {
            $projectId = (New-Guid).ToString()

            $projectXml = "<?xml version=""1.0"" encoding=""utf-8""?>
                <Project ToolsVersion=""4.0"" DefaultTargets=""Build"" xmlns=""http://schemas.microsoft.com/developer/msbuild/2003"">
                  <PropertyGroup>
                    <Configuration Condition="" '`$(Configuration)' == '' "">Production</Configuration>
                    <SchemaVersion>2.0</SchemaVersion>
                    <ProjectGuid>{$projectId}</ProjectGuid>
                    <OutputPath>bin\</OutputPath>
                    <Name>SSASProject</Name>
                  </PropertyGroup>
                  <PropertyGroup Condition="" '`$(Configuration)' == 'Development' "">
                        <OutputPath>bin\</OutputPath>
                    </PropertyGroup>
                  <ItemGroup>
                    <Compile Include=""$modelFileName"">
                      <SubType>Code</SubType>
                    </Compile>
                  </ItemGroup>
                  <Import Project=""`$(MSBuildExtensionsPath)\Business Intelligence Semantic Model\1.0\Microsoft.AnalysisServices.VSHostBuilder.targets"" />
                </Project>"
   

             $projectXml | Out-File "$outputPath\ssasproject.smproj" -Force
        }       

        Write-Verbose "Process finished"
    }
    finally
    {
        if ($server) { $server.Dispose() }
    }   
}

Function Export-PBIDesktopToCSV
{
<#
.SYNOPSIS
A way to export all your Power BI Desktop model tables into CSV files
.DESCRIPTION
A way to export all your Power BI Desktop model tables into CSV files
.PARAMETER pbiDesktopWindowName
Power BI Desktop window name, wildcards can be used. Ex: "*name*"
.PARAMETER tables
The tables to be exported - if empty all the tables get exported
.PARAMETER outputPath
Path to the output folder
.EXAMPLE
Export-PBIDesktopToCSV -pbiDesktopWindowName "*Van Arsdel*" -outputPath ".\CSVOutput"
#>

    [CmdletBinding()]
    param(                
        [Parameter(Mandatory = $true)]        
        [string]
        $pbiDesktopWindowName,
        [Parameter(Mandatory = $false)]        
        [string[]] $tables,
        [Parameter(Mandatory = $false)]        
        [string] $daxQuery,
        [Parameter(Mandatory = $true)]    
        [string] $outputPath        
      )
        
    $obj = Get-PBIDesktopTCPPort $pbiDesktopWindowName
    $port = $obj.Port
    
    $dataSource = "localhost:$port"
    
    Write-Verbose "Connecting into PBIDesktop TCP port: '$dataSource'"
    
    $ssasConnStr = "Provider=MSOLAP;data source=$dataSource;"
         
    $ssasDBId = (Invoke-SQLCommand -providerName "System.Data.OleDb" -connectionString $ssasConnStr `
        -executeType "Query" -commandText "select DATABASE_ID from `$SYSTEM.DBSCHEMA_CATALOGS").Database_id

    $ssasConnStr += "Initial Catalog=$ssasDBId"
    
    if ($tables -eq $null -or $tables.Count -eq 0)
    {
        $modelTables = Invoke-SQLCommand -providerName "System.Data.OleDb" -connectionString $ssasConnStr -executeType "Query" -commandText "select [Name] from `$SYSTEM.TMSCHEMA_TABLES"
        
        $tables = $modelTables |% {$_.Name}
    }

    if([System.IO.Path]::HasExtension($outputPath))
    {
        $outputFile = [System.IO.Path]::GetFileNameWithoutExtension($outputPath)
        $outputPath = [System.IO.Path]::GetDirectoryName($outputPath)
    }
    else
    {
        $outputFile = "DaxQuery"
    }

    if (-not (Test-Path $outputPath))
    {
        [System.IO.Directory]::CreateDirectory($outputPath) | Out-Null
    }

    if (![string]::IsNullOrEmpty($daxQuery))
    {
        $tables = @($outputFile)
    }
        
    $tables |% {
    
        try
        {
            
            $daxTableName = $_                                
        
            Write-Verbose "Moving data from '$daxTableName' into CSV File"
        
            $cmd = "EVALUATE('$daxTableName')"

            if (![string]::IsNullOrEmpty($daxQuery))
            {
                $cmd = $daxQuery
            }
            
            $reader = Invoke-SQLCommand -providerName "System.Data.OleDb" -connectionString $ssasConnStr `
                -executeType "Reader" -commandText $cmd 
        
            Write-Verbose "Copying data from into '$tableCsvPath'"
  
            $tableCsvPath = "$outputPath\$daxTableName.csv"

            $textWriter = New-Object System.IO.StreamWriter($tableCsvPath, $false, [System.Text.Encoding]::UTF8)

            $csvWriter = New-Object CsvHelper.CsvWriter($textWriter)                   
  
            $csvWriter.Configuration.CultureInfo.NumberFormat.NumberDecimalSeparator="."

            $rows=0

            $fieldCount=0

            if ($reader.Read())
            {
                $fieldCount=$reader.FieldCount

                for ($fieldOrdinal = 0; $fieldOrdinal -lt $fieldCount; $fieldOrdinal++)
                {
                    $colName=$reader.GetName( $fieldOrdinal ).Replace("[","").Replace("]","")
                    $csvWriter.WriteField( $colName );                 
                }

                $csvWriter.NextRecord();
            
                $rows++

                for ($fieldOrdinal = 0; $fieldOrdinal -lt $fieldCount; $fieldOrdinal++)
                {
                    $fieldValue=$reader[$fieldOrdinal ];             
                    $csvWriter.WriteField($fieldValue);                 
                }

                $csvWriter.NextRecord();

                if($rows % 5000 -eq 0)
                {
                    Write-Verbose "Inserted $rows rows into '$tableCsvPath'... "
                }
                
            }

            Write-Verbose "Fields in dataset '$fieldCount'"

            while($reader.Read())
            {
                $rows++

                for ($fieldOrdinal = 0; $fieldOrdinal -lt $fieldCount; $fieldOrdinal++)
                {                    
                    $fieldValue=$reader[$fieldOrdinal ];             
                    $csvWriter.WriteField($fieldValue);                 
                }

                $csvWriter.NextRecord();

                if($rows % 5000 -eq 0)
                {
                    Write-Verbose "Inserted $rows rows into '$tableCsvPath'... "
                }

            }             
        
            Write-Verbose "Inserted $rows rows into '$tableCsvPath' "
        }
        finally
        {                
            if ($reader -ne $null)
            {
                $reader.Dispose()
            }

            if ($textWriter -ne $null)
            {
                $textWriter.Dispose()
            }

        }
        
    }                    
}

Function Get-PBIDataSetFromPBIDesktop 
{
<#
.SYNOPSIS
A quick way to convert a Power BI Desktop file in a REST API enabled dataset
.DESCRIPTION
A quick way to convert a Power BI Desktop file in a REST API enabled dataset
.PARAMETER datasetName
Name to Dataset
.PARAMETER pbiDesktopWindowName
Power BI Desktop window name, wildcards can be used. Ex: "*name*"
.EXAMPLE
Get-PBIDataSetFromPBIDesktop -datasetName $datasetName -pbiDesktopWindowName "*RealTime*"
#>

[CmdletBinding()]
param(                
    [Parameter(Mandatory = $true)]        
    [string]
    $pbiDesktopWindowName,
    [Parameter(Mandatory = $true)]        
    [string]$datasetName
  ) 
 
    #get port and database model
    
    $obj = Get-PBIDesktopTCPPort $pbiDesktopWindowName
    $port = $obj.Port

    $dataSource = "localhost:$port"
    
    $server = New-Object Microsoft.AnalysisServices.Tabular.Server

    $server.Connect($dataSource)

    $database = $server.Databases[0]

    $dataSetSchema = @{

        name = $datasetName

        ;tables = @()

        ;relationships = @()
    }
    
    #for each Relationships

    $relationships = $database.Model.Relationships | ? {$_.ToTable.Name -NotMatch "LocalDateTable"}

    $relationships |% {

        $relationship = $_

        $props = @{ 

              name = $relationship.Name

            ; fromTable = $relationship.FromTable.Name

            ; fromColumn = $relationship.FromColumn.Name

            ; toTable = $relationship.ToTable.Name

            ; toColumn = $relationship.ToColumn.Name
            
            ; crossFilteringBehavior = $relationship.CrossFilteringBehavior.ToString()
        }
        
        $dataSetSchema.relationships +=  $props
    }

    #for each tables

    $tables = $database.Model.Tables | ? {!$_.Name.StartsWith("DateTableTemplate") -and !$_.Name.StartsWith("LocalDateTable")}

    $tables |% {
            
        $table = $_
        
        $newTable = @{

            name = $table.Name

            ;columns = @()

            ;measures = @()
        }

        #for each columns

        $columns = $table.Columns | ? {!$_.Name.StartsWith("RowNumber")}

        $columns |% {

            $column = $_

            $props = @{ 
                name = $column.Name

              ; dataType = $column.DataType.ToString()

              ; isHidden = $column.isHidden
            }

            $newTable.columns +=  $props
            
        }

        #for each measures

        $table.Measures |% {

            $measure = $_

            $props = @{ 
                name = $measure.Name

              ; expression = $measure.Expression

              ; formatString = $measure.FormatString
            }

            $newTable.measures +=  $props

        }

        $dataSetSchema.tables += $newTable
    }

   $dataSetSchema
}
Function Export-PBIDesktopToSQL
{      
<#
.SYNOPSIS
A way to export all your Power BI Desktop model tables into a SQL Server Database
.DESCRIPTION
A way to export all your Power BI Desktop model tables into a SQL Server Database
.PARAMETER pbiDesktopWindowName
Power BI Desktop window name, wildcards can be used. Ex: "*name*"
.PARAMETER tables
The tables to be exported - if empty all the tables get exported
.PARAMETER sqlConnStr
The SQL Server connection string
.PARAMETER sqlSchema
The target sql server schema where all the tables will be created (if not exists)
.EXAMPLE
Export-PBIDesktopToSQL -pbiDesktopWindowName "*Van Arsdel*" -sqlConnStr "Data Source=.\sql2017; Initial Catalog=Dummy; Integrated Security=true" -sqlSchema "stg" -Verbose
#>

    [CmdletBinding()]
    param
    (
        [Parameter(Mandatory = $true)]        
        [string]
        $pbiDesktopWindowName,
        [Parameter(Mandatory = $false)]        
        [string[]] $tables,        
        [Parameter(Mandatory = $false)]        
        [string[]] $destinationTables,
        [Parameter(Mandatory = $true)]        
        [string]
        $sqlConnStr,
        [Parameter(Mandatory = $false)]        
        [string]
        $sqlSchema = "dbo",
        [Parameter(Mandatory = $false)]        
        [hashtable]
        $forceDataTypes                
    )

    $obj = Get-PBIDesktopTCPPort $pbiDesktopWindowName
    $port = $obj.Port
    
    $dataSource = "localhost:$port"
    
    Write-Verbose "Connecting into PBIDesktop TCP port: '$dataSource'"
    
    $ssasConnStr = "Provider=MSOLAP;data source=$dataSource;"
         
    $ssasDBId = (Invoke-SQLCommand -providerName "System.Data.OleDb" -connectionString $ssasConnStr `
     -executeType "Query" -commandText "select DATABASE_ID from `$SYSTEM.DBSCHEMA_CATALOGS").Database_id

    $ssasConnStr += "Initial Catalog=$ssasDBId"
    
    if ($tables -eq $null -or $tables.Count -eq 0)
    {
        $modelTables = Invoke-SQLCommand -providerName "System.Data.OleDb" -connectionString $ssasConnStr -executeType "Query" -commandText "select [Name] from `$SYSTEM.TMSCHEMA_TABLES"
        
        $tables = $modelTables |% {$_.Name}
    }
        
    $i = 0

    $tables |% {
    
        try
        {

            $daxTableName = $sqlTableName = $_                

            if ($destinationTables -and $destinationTables.Count -gt 0)
            {
                $sqlTableName = $destinationTables[$i]
            }
        
            $sqlTableNameWithSchema = "[$sqlSchema].[$sqlTableName]"
        
            Write-Verbose "Moving data from '$daxTableName' into '$sqlTableNameWithSchema'"
        
            $reader = Invoke-SQLCommand -providerName "System.Data.OleDb" -connectionString $ssasConnStr `
                -executeType "Reader" -commandText "EVALUATE('$daxTableName')" 
        
            $rowCount = Invoke-SQLBulkCopy -connectionString $sqlConnStr -tableName $sqlTableNameWithSchema -data @{reader=$reader} -forceDataTypes $forceDataTypes -Verbose
        
            Write-Verbose "Inserted $rowCount rows"
        
        }
        finally
        {            
            if ($reader -ne $null)
            {
                $reader.Dispose()
            }

            $i++
        }        
    }

}

Function Get-PBIDesktopTCPPort
{  
<#
.SYNOPSIS
Returns the Power BI Desktop Analysis Services Instance TCP Port
.DESCRIPTION
Returns the Power BI Desktop Analysis Services Instance TCP Port
.PARAMETER pbiDesktopWindowName
Power BI Desktop window name, wildcards can be used. Ex: "*name*"
.EXAMPLE
Get-PBIDesktopTCPPort -pbiDesktopWindowName "*VanArsdel - Sales*"
#>

    [CmdletBinding()]
    param
    (
        [Parameter(Mandatory = $false)]        
        [string]
        $pbiDesktopWindowName    
    )
    
    $pbiProcesses = get-process |? ProcessName -eq "PBIDesktop" | select Id, ProcessName, MainWindowTitle |? MainWindowTitle -ne "" 
        
    $pbiProcessesPorts = Get-NetTCPConnection -OwningProcess @($pbiProcesses | Select -ExpandProperty Id) |? State -eq "Established" | Select OwningProcess, RemotePort
    
    if ($pbiProcesses.Count -eq 0 -or $pbiProcessesPorts.Count -eq 0)
    {
        throw "No PBIDesktop windows opened"
    }
    
    $matchedWindows = @($pbiProcesses |? { $_.MainWindowTitle -like $pbiDesktopWindowName -or [string]::IsNullOrEmpty($pbiDesktopWindowName) })
    
    if ($matchedWindows.Count -eq 0)
    {
        throw "No PBIDesktop window that match '$pbiDesktopWindowName'"
    }
    
    # Select the first match
    
    $matchedWindows |% {
        
        $matchedProcess = $_

        $matchedProcessTitle = $matchedProcess.MainWindowTitle
        
        Write-Verbose "Processing PBIDesktop file: '$matchedProcessTitle'"
        
        $processPorts = $pbiProcessesPorts |? OwningProcess -eq $matchedProcess.Id
        
        if ($processPorts.Count -eq 0)
        {
            throw "No TCP Port for PBIDesktop process '$matchedProcessTitle"
        }
        
        $port = $processPorts[0].RemotePort
        
        Write-Output @{WindowTitle=$matchedProcessTitle; Port = $port}
    }
}

Function Export-PBIDesktopODCConnection
{  
<#
.SYNOPSIS
Exports a PBIDesktop ODC connection file
.DESCRIPTION
Exports a PBIDesktop ODC connection file
.PARAMETER pbiDesktopWindowName
Power BI Desktop window name, wildcards can be used. Ex: "*name*"
.PARAMETER path
ODC file path to be created
.EXAMPLE
Export-PBIDesktopODCConnection -pbiDesktopWindowName "*VanArsdel - Sales*"
#>

    [CmdletBinding()]
    param
    (
        [Parameter(Mandatory = $false)]        
        [string]
        $pbiDesktopWindowName,
        [Parameter(Mandatory = $false)]        
        [string]
        $path    
    )
    
    $pbiDesktopWindows = Get-PBIDesktopTCPPort -pbiDesktopWindowName $pbiDesktopWindowName

    if ([string]::IsNullOrEmpty($path))
    {
        $path = "."
    }

    $pbiDesktopWindows |% {

        $obj = $_
        
        $port = $obj.Port

        $odcXml = "<html xmlns:o=""urn:schemas-microsoft-com:office:office""xmlns=""http://www.w3.org/TR/REC-html40""><head><meta http-equiv=Content-Type content=""text/x-ms-odc; charset=utf-8""><meta name=ProgId content=ODC.Cube><meta name=SourceType content=OLEDB><meta name=Catalog content=164af183-2454-4f45-964a-c200f51bcd59><meta name=Table content=Model><title>PBIDesktop Model</title><xml id=docprops><o:DocumentProperties xmlns:o=""urn:schemas-microsoft-com:office:office"" xmlns=""http://www.w3.org/TR/REC-html40""> <o:Name>PBIDesktop Model</o:Name> </o:DocumentProperties></xml><xml id=msodc><odc:OfficeDataConnection xmlns:odc=""urn:schemas-microsoft-com:office:odc"" xmlns=""http://www.w3.org/TR/REC-html40""> <odc:Connection odc:Type=""OLEDB"">
        <odc:ConnectionString>Provider=MSOLAP;Integrated Security=ClaimsToken;Data Source=localhost:$port;MDX Compatibility= 1; MDX Missing Member Mode= Error; Safety Options= 2; Update Isolation Level= 2; Locale Identifier= 1033</odc:ConnectionString>
        <odc:CommandType>Cube</odc:CommandType> <odc:CommandText>Model</odc:CommandText> </odc:Connection> </odc:OfficeDataConnection></xml></head></html>"
   
                
        Write-Verbose "Exporting ODC for window '$($obj.WindowTitle)'"
        
        $odcFile = "$path\$($obj.WindowTitle).odc"

        $odcXml | Out-File $odcFile -Force    

    }    
}


#region Private

Function Get-MCodeFromPBIDataSource
{    
    param
    (
        [Parameter(Mandatory = $true)]                
        $partition
    )

    $connStr = $partition.Source.DataSource.ConnectionString

    $connStrBuilder = New-Object System.Data.Common.DbConnectionStringBuilder

    $connStrBuilder.set_ConnectionString($connStr)

    $mashupBase64 = [string]$connStrBuilder["mashup"]

    $bytes = [System.Convert]::FromBase64String($mashupBase64)
    
    $mExpression = Get-ZipSection -bytes $bytes -entryName "Section1.m"
 
    $mExpression = Get-CleanMCode -mcode $mExpression

    Write-Output $mExpression
}

Function Get-ZipSection ($bytes, [string] $entryName, [System.Text.Encoding] $encoding = [System.Text.Encoding]::UTF8)
{  
    try {        
        Add-Type -Assembly 'System.IO.Compression' 

        $ms = New-Object System.IO.MemoryStream (,$bytes)

        $zip = New-Object System.IO.Compression.ZipArchive($ms)

        $section = @($zip.Entries |? { $_.Name -eq $entryName })

        if ($section.Count -eq 0)
        {
            throw "Cannot find entry '$entryName'"
        }

        $deflateStream = $section[0].Open()

        $streamReader = New-Object System.IO.StreamReader($deflateStream, $encoding) 
                
        $entryText = $streamReader.ReadToEnd()

        Write-Output $entryText
    }
    finally {
        if ($streamReader) { $streamReader.Dispose() }
        if ($deflateStream) { $deflateStream.Dispose() }
        if ($zip) { $zip.Dispose() }
        if ($ms) { $ms.Dispose() }
    }         
}
Function Get-CleanMCode{    
    param
    (
        [Parameter(Mandatory = $true)]        
        [string]
        $mcode 
    )

    $colletion = @()
    $hiddenTable = $mcode -split "shared"

    if($hiddenTable.Count -eq 2){

        #match
        $match = [System.Text.RegularExpressions.Regex]::Match($mcode,'let(?s:.)*Table.FromValue[^\,]*')
        
        if($match.Success){ $value=$match.Value }

        #fisrt Mcode
        $first = $value.IndexOf('AutoRemovedColumns1 =')

        $first = $value.Substring(0,$first).Trim()

        $first = $first.Substring(0,$first.Length-1)

        #last Mcode
        $last = $value.Split('(')

        $last = $last[$last.Count-1]

        $M = New-Object PSObject -Property @{
             hiddenTable = $false
             expression = "$first in $last"
             name = ""
        }

        $colletion += $M

    }else{ # contains hidden tables;

       # lets create expressions for each shared
      
       #$ex = $mcode.Split(';')
       $ex = $mcode -split "shared "

        For ($i=1; $i -le $ex.Count-1; $i++) {

            if(-Not ($ex[$i].Contains("IsParameterQuery")))
            {
                $ex[$i] = $ex[$i].Trim()

                $tam = $ex[$i].indexOf('=') + 1

                #Remove extra auto code coming from PowerBI
                $t = ($ex[$i].Substring($tam,$ex[$i].Length-$tam)) -split "AutoRemovedColumns1"

                $dd = $t[0].Trim().Split([Environment]::NewLine)
                
                #Remove last comma
                $t[0] = $t[0].Trim();
                $t[0] = $t[0].Substring(0, ($t[0].Length)-1)

                #Get last identifier
                $lastId = $dd[-1].Split('=')

                #Fix the ending
                if(-Not ($t[0] -match '[\s.*]in[\s.*](?=([^"\\]*(\\.|"([^"\\]*\\.)*[^"\\]*"))*[^"]*$)'))
                { 
                    $t[0] += [Environment]::NewLine + " in " + $lastId[0].Trim()
                }

                $M = New-Object PSObject -Property @{
                    hiddenTable = $true
                    expression = $t[0]
                    name = $ex[$i].Split('=')[0].Replace('#"','').Replace('"','')
                } 

                $colletion += $M
            } else { # parameter expressions
                $colletion += New-Object PSObject -Property @{
                    hiddenTable = $true
                    expression = $ex[$i].Split('=',2)[1].Replace(";","").Replace("`r","").Replace("`n","").Trim()
                    name = $ex[$i].Split('=')[0].Replace('#"','').Replace('"','')
                }
            }
         }
    }

    $colletion
}

#endregion