Adding tables in Power Query using Tabular Editor

---

CREATING OR MODIFYING STATIC DATA TABLES

…using Tabular Editor to modify Power Query or DAX Code

---

Recently, a colleague asked how to replace ‘enter data’ tables with Power Query / M code using Tabular Editor. They managed a dataset with incremental refresh using Tabular Editor 2, and needed to revise these tables plus add new ones for specific reporting functionality.

This is of course easier to do in DAX, but the ask was specific to how this could be done in M, from TE2.

---

STEPS

CREATE/MODIFY A STATIC DATA TABLE FROM TABULAR EDITOR

STEP 1

Connect to the dataset in Power BI Premium / PPU
Since you’re using XMLA read/write, you need premium to do this.

STEP 2

Right-click the ‘Tables’ folder of the TOM Explorer and select ‘Create New… > Table’ (Alt + 5).

You can skip this step if you are modifying an existing ‘Enter Data’ table.

STEP 3

In the partition, under ‘Expression Editor’, add Power Query code to specify the table, using one of the methods outlined in the next section (i.e. #table() or Table.FromRows()).

If you are replacing or modifying an existing ‘Enter Data’ table, replace the existing M code / Power Query with one like the below. At the bottom of the article (next section) is an explanation on how to specify a static data table in Power Query.

STEP 4

If you need to add columns, right-click the table and select ‘New … > Data Column’

Create each of the data columns for the table

STEP 5

For each column, under the ‘Properties’ pane, for the ‘Source Column’ property, specify the name of the column as it appears in the M expression.

STEP 6

Write the changes to the dataset; save your changes.

STEP 7

Refresh the model by running the below C# script in the ‘Advanced Scripting’ tab, while the new table is selected. Tabular Editor 2 will not be responsive while this script is executing, during that time it is refreshing the table. This script is modified from the helpful TE2 Script Snippets on the docs site.

It is suggested that you save this as a ‘Custom Action’ (macro) so you can perform single table refreshes on your model from within TE2.

// C# Script: Refresh selected table
// Reference: https://docs.tabulareditor.com/te2/Useful-script-snippets.html#querying-analysis-services
  
// Type of refresh - full = full process
var
 type = 
"full"
;

// Dynamic values of the dataset & table name
var
 database = Model.Database.Name;
var
 table = Selected.Table.Name;

var
 tmsl = 
"{ \"refresh\": { \"type\": \"_type\", \"objects\": [ { \"database\": \"_db\", \"table\": \"_table\" } ] } }"
    .Replace(
"_type"
, type)
    .Replace(
"_db"
, database)
    .Replace(
"_table"
, table);

ExecuteCommand(tmsl);

STEP 8

Query the table so that you can see it worked.

You can do this in DAX from TE2 also using a C# script, below:

// Evaluates the selected table and outputs result in a pop-up 
EvaluateDax(Selected.Table.DaxObjectName).Output();

---

MORE CONTEXT: ADDING STATIC DATA TABLES

There are a number of ways to add new data tables to a Power BI dataset:

1. Adding static data tables via Power Query

   • Enter Data: Using the GUI in Power BI desktop to enter and load the data.
     This is favored because it’s easy, quick, and lets you copy/paste right into Power BI. However, it’s hard to change.
     There’s a nice overview of how it works in this community post. To summarize:

     • A base64-encoded string is generated
       ”i45WSkvNyckvV0jPT8rJzFOKjQUA”

     • A binary is created from this string
       Binary.FromText("i45WSkvNyckvV0jPT8rJzFOKjQUA", BinaryEncoding.Base64)

     • It is decompressed with the Deflate compression type
       Binary.Decompress(Binary.FromText("i45WSkvNyckvV0jPT8rJzFOKjQUA", BinaryEncoding.Base64), Compression.Deflate)

     • The decompressed binary information is converted to a list (of lists)
       Json.Document(Binary.Decompress(Binary.FromText("i45WSkvNyckvV0jPT8rJzFOKjQUA", BinaryEncoding.Base64), Compression.Deflate))

     • The table is created, with column names & data types
       Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkvNyckvV0jPT8rJzFOKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"hi there" = _t])

   • Excel: This is just a connection to an Excel table (in SharePoint, for example) that will have the data needed. It might be favored because a business user can easily change the table values.

   • Writing M Code: Most frequently seen with date tables, this generates the table based on a set of parameters or list of lists, using i.e. Table.FromRows() or Table.FromRecords().
     
     There is a good article from Chris Webb written in 2016 that writes about this topic, as well, specifically using #table().
     
     An example using Table.FromRows():

  = Table.FromRows(
  {
    {
"Klonk"
, 
7381
}, 
    {
"Bonk"
, 
 7384
}, 
    {
"Stonk"
, 
7390
}
  }, 
  
type
 
table
    [#"Goblin" = 
text
, 
     #"Employee ID" = Int64.Type]
) 

2. Adding static data tables via DAX

• Tuple: The simplest, single-column table. Can further modify with SELECTCOLUMNS to rename the default [Value] column name, or add an index with RANKX.

EVALUATE
SELECTCOLUMNS (
    {
        "Klonk",
        "Bonk",
        "Stonk"
    },
    "Re-named Column", [Value]
)

• There are of course many other functions that enable this:

  • DATATABLE function

  • UNION & ROW - etc
    There is a SQLBI article that looks at these, as well. Check it out.

---

TO CONCLUDE

• It’s possible to create or modify a static table (i.e. created with Enter Data) in a Power BI dataset from Tabular Editor 2 or 3.

• You can refresh tables and execute DAX queries from TE2 using C# scripts

• It’s easier to manage static tables with the DAX functions available, but there might be a reason or requirement for why they are specified in M code, instead.

---