Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Nolock

Power Query Enter Data Explained

There are many things you do so often that you don’t think how they work. They just work and that is enough. Not today.
I would like to discuss a Power Query query which is automatically generated when you create a new table in Power BI Desktop via the Enter Data button.

 

1.png

 

The code is the following:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSk7NyTE0VNKBMIyADEOlWB2IuBFM3AgkbqQUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Col1 = _t, Col2 = _t, Col3 = _t])
in
    Source

 

When you open the Power Query Advanced Editor, you see a long line. The beauty of the code comes out when you format it properly.

 

let
    Source = 
        Table.FromRows(
            Json.Document(
                Binary.Decompress(
                    Binary.FromText(
                        "i45WSk7NyTE0VNKBMIyADEOlWB2IuBFM3AgkbqQUGwsA", 
                        BinaryEncoding.Base64
                    ), 
                    Compression.Deflate
                )
            ), 
            let 
                _t = ((type text) meta [Serialized.Text = true]) 
            in 
                type table [Col1 = _t, Col2 = _t, Col3 = _t]
        )
in
    Source

 

Let’s rewrite the code that it has only one expression per step:

 

let
    Base64EncodedBinary = "i45WSk7NyTE0VNKBMIyADEOlWB2IuBFM3AgkbqQUGwsA",
    BinaryFromText = Binary.FromText(Base64EncodedBinary, BinaryEncoding.Base64),
    DecompressBinary = Binary.Decompress(BinaryFromText, Compression.Deflate),
    JsonDocument = Json.Document(DecompressBinary),
    TableFromRows = 
        Table.FromRows(
            JsonDocument, 
            let 
                _t = ((type text) meta [Serialized.Text = true]) 
            in 
                type table [Col1 = _t, Col2 = _t, Col3 = _t]
        )
in
    TableFromRows

 

Firstly, we have a Base64-encoded string. Is it convertible into a string which is readable for a human? Not really – as you can see on the following screenshot. It is binary data which is Base64-encoded.

 

2.png

 

In the next step, we create a binary from this encoded string. Next, it will be decompressed by the function Binary.Decompress. This function needs a binary value and a compression type. In this case the Deflate compression type is used.

Before we continue, I would like to show you what the decompressed data is. We can convert it into a text using the function Text.FromBinary.

 

3.PNG

 

The result is a list of lists where the inner list is a row of the future table. You can compare it with the first screenshot. What is very important is that it doesn’t contain column names. They will come soon.

Next step is the function Json.Document. It converts the decompressed binary data into a list of lists. The same what we have already seen with Text.FromBinary, but this time it is a regular list, not just text.

 

We are almost done. The last step is the function Table.FromRows. The definition says: “Creates a table from the list rows where each element of the list is an inner list that contains the column values for a single row.”

If we use the function with the mandatory parameter only, the result is:

 

4.PNG

 

It looks like the table we wanted to create. But something is wrong. What do we miss? Hmm, the column names. They are gone. We have to define them.

The function Table.FromRows has an optional parameter called columns of the data type any. It can be a list of column names like in the documentation.

 

Table.FromRows({ {1, "Bob", "123-4567"},{2, "Jim", "987-6543"}},{"CustomerID", "Name", "Phone"})

 

But it can also be a type table with a record which contains column names and their data types.

 

Table.FromRows({{1, "Bob", "123-4567"}, {2, "Jim", "987-6543"}}, type table [CustomerID = number, Name = text, Phone = text])

 

The generated 4 lines code looks more complicated, but it does the same.

 

let 
    _t = ((type text) meta [Serialized.Text = true]) 
in 
    type table [Col1 = _t, Col2 = _t, Col3 = _t]

 

The fourth line says we have a table having 3 columns named Col1, Col2, and Col3. All columns have the same data type _t.

How is the data type _t defined? It is data type text. It has also another value Serialized.Text = true associated with the data type – so called metadata. (The metadata is usually used when you want to document your code. More about that https://docs.microsoft.com/en-us/power-query/handlingdocumentation) Unfortunately, the use of this metadata is not documented. The only thing I can do is to try setting it to false. And it works as before. If you remove the metadata, it works as well. Maybe, there is an edge case in which it has its reason, who knows.

Please, let me know in the comments if you know where to find these special metadata properties used in build-in functions.

 

This short analysis of a one-liner generated by the Power Query Editor was created out of curiosity. You will never want to change the generated code, but I think it is good to understand what happens under the hood.