A while ago I’ve received an email from Jason Alvarez asking me if I knew a way to change Power Query connections inside an Excel file. The problem is similar to my previous post on Mass changing pivot table connection strings in Excel using PowerShell. Turns out you can and he was able to find this solution:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

#root path to the collection of excel docs:
$rootPath = "C:\Temp\PowerQueryDemo\"

#Current query file path:
$currentPath = "C:\Temp\PowerQueryDemo\"
#Updated query file path:
$newPath = "C:\Temp\PowerQueryDemo\PowerQueryNewSource"

Get-ChildItem -Path $rootPath -Recurse -Include *.xlsx | ForEach-Object{
    $docPath = $_.FullName

    ## Create an instance of excel:
    $ExcelAppInstance = New-Object -ComObject "Excel.Application"
    $ExcelAppInstance.Visible = $false

    ## Open the workbook object within our Excel instance:
    $workbook = $ExcelAppInstance.workbooks.Open($docPath)

    ##iterate the list of queries embedded in the workbook,
    ##updating the file path to our new one:
    $workbook.Queries | ForEach-Object{
        Write-Output $_.Formula
        Write-output $_.Formula.replace($xPath,$tPath)
        $_.Formula = $_.Formula.replace($xPath,$tPath)
    }

    $workbook.Save()
    $workbook.Close()
    $ExcelAppInstance.Quit()
}

The key here is the Queries property inside the Workbook object.

As I explained in a previous post about Purview with the official Snowflake connector you need to create a scan per database/schema pair. In the customer where I was working there are a few hundred databases plus their schemas.

Doing this by using the UI would mean that we will be wasting a lot of time clicking away and be prone to forget some database/schema pair.

I’ve explored the documentation about the Scans - REST API and the rest of this post is just some PowerShell code to automate the creation of scans and their schedules in Purview.

A few days ago I had to add a new team member to more that 50 workspaces. I started dying inside just thinking of going to each workspace and add it using the portal ui.

Here’s a little snippet that saved me a few hundred clicks:

Login-PowerBI

$workspaces = Get-PowerBIWorkSpace

$user = "mySpecial.User@MyDomain.com"

foreach($workspace in $workspaces)
{
    Write-Host "Adding User to : " $workspace.Name
    try
    {
        Add-PowerBIWorkspaceUser -Id $workspace.Id -UserPrincipalName $user -AccessRight Member
    }
    catch
    {
        Write-Host "Message: [$($_.Exception.Message)"] -ForegroundColor Red
    }
}

Adapt it to your needs.

Do you really can diff Power BI files?

While working with some customers a few times this question came up: “How can I diff my current file with a older one? I really want to compare my files, to know what changed from one to another.”

Many many people working with Power BI end up with a directory full of files looking like: “myPowerBIFileV1.pbix”, “myPowerbifilev2.pbix”… “myPowerbifilevN.pbix”. The scenario wull get worse if we have two or more people working in the same file, and until recently my answer was always the same: “You can’t”.

Warning: Unprotected Private Key File

As an avid user of OpenSSH on Windows after last update I’ve started seen this on my PowerShell:

Windows PowerShell
Copyright (C) Microsoft Corporation. All rights reserved.

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@         WARNING: UNPROTECTED PRIVATE KEY FILE!          @
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
Permissions for 'C:\\Users\\My User/.ssh/id_rsa' are too open.

File System Security PowerShell Module

Because file and folder permissions are not easy with PowerShell I’m using the File System Security PowerShell Module. You can install from the gallery using the command:

The Problem

As Business Intelligence consultants after a migration to a brand new server inevitably we get asked by the users: “Do why have to redo all my pivot tables in Excel again? All my workbooks stopped working…”

Well, they can but they don’t need to. Where’s a simple code to replace the part that matters. This will iterate all the Excel files in the folder where the Powershell Script is located and and apply the substitution to all the OLEDBConnections that exist in each file:

Introduction

In these days developing in SSIS, you should be using a generation framework (BIML or EzApi. The benefits era a bit obvious:

1. Your packages will have consistent layout and logic
2. It’s easy and fast to apply the same change to all packages
3. No more mouse programming (almost :))
4. It’s the right way to do metadata driven ETL

The problem

In a client I had a project with 97 packages. Each one of them started as copy of the first one. The request was to change the value of a single variable on all of them.

Introduction

For the ones unware of SFTP (SSH File Transfer Protocol), it is a secure channel with SSH2 encryption established between two machines. You can think of it as FTP with encryption and a few more differences. For more information about this you can read this blog post.

There are some alternatives to get this done, ones paid and others free. I will focus on the use of the excellent SSH.NET because I couldn’t find a good guide to get it to work with SSIS. It’s worth mencioning WinSCP as well, and the excellent guide to make it work with Integration Services.