Power Query for Azure Tables and
Updating Column Names

I recently did some PowerBI work using Azure Table Storage. In the query editor, it seemed that each table I pulled in from Azure Tables had a very similar process.:

  1. Get the list of tables available for the storage account.
  2. Select a table
  3. Select the Content of the table (excluding the RowKey, PartitionKey, and timestamp columns)
  4. Expand the columns in the Content
  5. Rename the columns

Azure table column names don’t allow spaces, but they are case-sensitive, so I found that if I was careful to use PascalCasing in my Azure Table columns (which I am careful to do in any storage container), I could adapt the steps above to make a function that would do all those things for me, just by providing the name of the account and the name of the table.

let GetAzTable = (account as text, table as text) =>
let
  Source = AzureStorage.Tables(account),
  Table1 = Source{[Name=table]}[Data],
  ContentOnly = Table.SelectColumns(Table1,{"Content"}),
  ColumnName = Table.ColumnNames(ContentOnly){0},
  Content = Table.Column(Table.FirstN(ContentOnly,100),ColumnName),
  ColumnsToExpand = List.Distinct(List.Combine(List.Transform(Content,
    each if _ is record then Record.FieldNames(_) else {}))),
  NewColumnNames = List.Transform(ColumnsToExpand, each NewColumnName(_)),
  ExpandedContent = Table.ExpandRecordColumn(ContentOnly, ColumnName, ColumnsToExpand,NewColumnNames)
in
  ExpandedContent
in GetAzTable

In this code, I reference another function that generates the new “nice” column name based on the column name in the Azure table. This function doesn’t do the name formatting, but it determines if a column should get a nice name or not. I use a naming convention for columns that are meant to be used internally – either as a join key or for a sort column, or for a measure calculation. This function returns the column name “as is” if it follows the naming convention for internal columns, or calls the function GetPascalName to make it a “nice” name.

let NewColumnName = (colName as text) as text =>
let
  Source = if Text.EndsWith(colName, "Key") or Text.EndsWith(colName,"Num") or Text.EndsWith(colName, "Sort") or Text.EndsWith(colName,"SortOrder") or colName = Text.Upper(colName) then colName else GetPascalName(colName)
in
  Source
in NewColumnName

Here is the cool code:

let PascalName = (pascalName as text) as text =>
let
  Source = Text.ToList(pascalName),
  Caps = { "A" .. "Z" },
  NewSource = List.Combine(List.Transform(Source, (x) => if List.PositionOf(Caps, x) >= 0 then {" ",x} else {x})),
  result = Text.Trim(Text.Combine(NewSource, ""))
in
  result
in PascalName

This code takes a text value and turns it into a list (of individual characters). Then it recombines the list of characters, adding a space before each capital letter. Finally, it trims the leading space.

This isn’t perfect by all means – it could be improved by only putting a space in front of the first capital in a series of capitals. A column named “FTPUrl” will come back as “F T P Url”. (Note that I’ve already caught the case of a column with ALLCAPS in the NewColumnName function.) Just looking at this edge case, I think the algorithm would need to put a space before the first and the last capital in a series of capitals (” FTP Url”, trimmed to “FTP Url”).

You can view the original post of the author here

Thank you for reading this post! If you enjoyed it, I encourage you to check out some of our other content on this blog. We have a range of articles on various topics that I think you’ll find interesting. Don’t forget to subscribe to our newsletter to stay updated with all of the latest information on Imaginet’s recent successful projects

Mike Diehl

Mike Diehl is the practice lead for Databases and Business Intelligence at Imaginet Resources, a Microsoft partner with offices in Dallas, Calgary, and Winnipeg. Mike has over 20 years of experience using Microsoft database technologies and is an expert in Agile Analytics, Scrum, Kanban, and Microsoft Azure DevOps.

discover more

Imaginet in Healthcare

Imaginet in Healthcare: Solving Complex Challenges with Custom Software, UX, DevOps, and Data Strategy

Healthcare organizations face a unique set of challenges: regulatory complexity, legacy systems, siloed data, and the constant pressure to improve patient outcomes while reducing operational costs. Technology can help, but…

Read More
Custom Software

The Future of Custom Software: Building for Agility, Not Just Functionality

Technology often evolves faster than most organizations can adapt. Just think about the advancements we have seen with artificial intelligence over the last few years alone. This means custom software…

Read More
What is Azure DevOps

What Is Azure DevOps Really Solving? A Business-Centric Look at Agile Delivery

Businesses are under constant pressure to deliver fast, reliable, and transparent software. This pressure will increase as technology continues to evolve and advance. Terms like DevOps, CI/CD, and agile delivery…

Read More

Let’s build something amazing together

From concept to handoff, we’d love to learn more about what you are working on.
Send us a message below or call us at 1-800-989-6022.