When importing Excel files using Power Query, any columns without a name in the first row will end up titled “Column123”, with an number at the end that increments for each column. This happens whether they contain actual data, or whether they’re just formatted columns that contain nothing at all.
If you’re importing a single file, you can rename or delete these columns as you need to. However, if you’re importing an entire folder of Excel files or the format changes unpredictably, you may want to automatically remove all o these columns at the end.
To do this, you’ll use the “Advanced Editor” to add a line of M-code:
"Remove Columns Named Column" = Table.RemoveColumns(#"Promoted Headers", List.FindText(Table.ColumnNames(#"Promoted Headers"), "Column")),
After you add this step, you may need to modify your “Changed Type” step to only set the type on columns you’re keeping, as it automatically mentions every single column by name.
Adding this step will remove the additional columns, dynamically, every time the data is imported. Now when you import a folder full of Excel files and some of them contain additional columns or rogue formatting, the step will drop them if they don’t get named.
After a Powershell session at SQL Saturday (Phoenix #131) this weekend, I’m now suddenly on the lookout for handy powershell commands. The first one lets you see the number of Active* sessions on your IIS site (* because HTTP is stateless, it’s really the number of connections that have been opened recently – not what’s currently active, which is likely next to zero. I tried browsing around on a test site and it showed only my one user connected).
To get the currently active user count, here’s the powershell:
# Ensure you use the server's actual name, not LOCALHOST, which won't work
$Servername = "Your Server Name"
$Sitename = "Name of your IIS Site"
Get-Counter "\\$ServerName\web service($SiteName)\current connections"