🪄🐈‍⬛ TnT #5 🧨

I have finally decided to get this Power Query TnT written down before I forget all about it for the 80-bazillionth time. This one covers how I quickly transform a list of Records into a Table of correctly typed columns, all in a single Power Query M action step!

The Problem

I get so frustrated every time that I make a call to an API, or otherwise wind up with a parsed JSON or XML response as a list of records that I need as a Table.

Now why would any reasonable person get frustrated, you ask? Does this seem to you to be a bit of a silly thing to get so worked up over?

Well, I’m glad you asked! …you did ask, right? 🤨 I hope so, because here it comes…

I get so worked up because of 👏 ALL 👏 THE 👏 EXTRA 👏 STEPS 👏 REQUIRED 👏 to transform the list into a table…

WITH all the fields as the correct data type.

WHY so many steps just to transform a list of records into a table, Microsoft?!? Why?

The Solution

The solution I stumbled across years ago, is quite simple really. Combine the type table declaration with the square bracket shorthand ([]) for Records, together in the column names parameter of the Table.FromRecords function.

toTable = Table.FromRecords(
    //name of the step returning the list of records I want to transform into a table
    results,

    //declaration that I am providing my columns as a table type
    type table [

        //list of each column name (in order of appearance) with the appropriate type declaration 
        name = text, 
        url = text
    ],

    //value to return for a missing field
    null
)

Et voilà! One-step my friends! One singular step! That’s all it takes!

Notice I get the exact same result as before. But now the ‘code’ is much cleaner, much easier to read, and so much easier to manage.

Now isn’t this approach so incredibly satisfying?!? 🎉🥰

Additional tidbits

I’m not sure how many of you now about the sharp (#) shortcuts for Power Query, also known as intrinsic functions, but there are a few, and they are extremely useful! I’ve provided links below if you want to look over the Microsoft Learn Documentation for them.

#date(2023, 7, 17) = 2023-Jul-17
#datetime(2023, 7, 17, 8, 0, 0) = 2023-Jul-17 8:00 AM
#datetimezone(2023, 7, 17, 8,0, 0, -4, 0) = 2023-Jul-17 08:00 EDT
#duration(17, 8, 0, 0) = 17.08:00:00 days
#time(8, 0, 0) = 8:00 AM or 08:00

The reason I brought these up here at the end, is because there is also one for declaring a table, which is #table. This function gets expressed as the following:

#table(type table [name = text, url = text], {{"Yer A Wizard, Cat!", "yerawizardcat.com"}})

Resulting in the table below:

I just thought I’d share with you all, and maybe we can both come back to this some day in the future, when we ultimately forget how it’s supposed to be done… yet again.😅