• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Static Tables in Power Query, Power Pivot and Power BI

You are here: Home / Power BI / Static Tables in Power Query, Power Pivot and Power BI
static data tables
March 31, 2021 by Philip Treacy

Ordinarily when you want to create a table in Power Query, Power Pivot or PBI, you'd write a query to load it from an external source.

But any time you have data that won't change (or changes rarely), you can use a static table. That is, a table that doesn't need a data source, it is created directly inside Power Query or the Data Model.

Watch the Video

Subscribe YouTube

Download Sample Files

Enter your email address below to download the sample file.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the files and code used in this post

Download the PBIX file.
Download the Excel Workbook.


For example, You might have a table which can be used to calculate bonus amounts when certain sales thresholds are met.

sample table data

This Threshold column is the $ value of sales you need to reach in order to get a bonus which is calculated by multiplying that threshold value by the rate.

Such data is only used for reference or calculations so there's really no need to load it from an external source.

In this post I'm going to show you 5 ways to create tables in Power Query, Power Pivot and Power BI without loading any data.

#table in Power Query

There are a number of ways to create a table in Power Query using functions like Table.FromList, Table.FromColumns, etc. but the way I find most flexible and use most often is the #table function.

In Excel open the Power Query editor and start a new blank query. Then open the Advanced Editor.

open the power query editor

create a blank query

open advanced editor

The syntax to create a table with #table is this

#table syntax

1 Start with the #table function name

2 Then declare that we're creating a type table

3 The column names

4 The data types of the columns

5 The data for the columns

If you want more columns then declare more in Section 3. To add more rows just add more lines in Section 5.

Filling in the data needed to create the Bonus Rates table

m code to create #table

Gives me this table

bonus rates table in power query

If at some point I do need to change a value in the table, just open the query and make the change.

Power Pivot

With Power Pivot it's as easy as pasting in the data.

First, copy the data from the sheet

Copy the data

Then open Power Pivot

Open Power Pivot

Because I've already copied data the Paste button is available

Paste button

so clicking that brings up this dialog window

Paste preview

Give the table a name and leave the Use first row as column headers checked. Then click OK and the data is entered into a new table.

table in power pivot

You can't make any changes to this table so if you need to alter it, you'll have to delete it and start over.

Close Power Pivot and back in Excel if I insert a pivot table

Insert pivot table

and select 'Use this workbooks data model'

create pivot table

In the Pivot Table Fields you can see that the table is there ready to be used.

pivot table fields

Power BI - Enter Data

In Power BI Desktop there's a button on the Home tab of the Ribbon called Enter data.

Power BI Desktop Enter data

Clicking on this brings up this dialog where you can type in data.

Your browser does not support the video tag.

You can move around the table using the arrow (cursor) keys on your keyboard, or cick into cells with you mouse.

You can give your columns names, moving the cursor to the right adds columns, and moving it down adds rows.

If you make a mistake you can delete the columns and rows.

The easiest way to enter data is to copy/paste. Copy the data from its source, then click on the top left of the table and paste.

Give the table a Name and then click the Load button.

Your browser does not support the video tag.

Once the table is loaded into PBI Desktop, you are left looking at an empty report screen. To check your table,click on the Transform data button to open the Power Query editor

Transform data

The table is there as entered but you can see the data type of the columns is Any so they have to be changed.

Power query table

If I open the Advanced Editor you'll see that the table is Stored as an Encoded, Compressed JSON Document and can't be edited.

table in power query as json encoded document

If you need to alter this table, click on the gear icon beside the Source step of the query, and it will open the Enter data dialog box again where you can make changes.

modify table in power query

DAX Table Constructor

The table constructor syntax is very simple

DAX table constructor

You start with the table name NewTable in this example image. The table contents is surrounded by curly braces {} where each line is a row in the table.

Each row of data is surrounded by parentheses/brackets and rows are separated by a comma.

The number of columns is determined by the number of values in each row.

To create the BonusRates table I use this

dax table

There isn't any way to name the columns when constructing the table in this way.

default column names

By default if there is only 1 column the column name is Value. If there is more than 1 column they are called Value1, Value2 etc

You can change the column names after the table is created by double clicking the column name and typing in the new name

rename columns

The data type of the column is determined by the data you enter

You can see here that Threshold is whole number, and Rate will be decimal

data types

If you have a column of mixed data types, the entire column is converted to a common data type.

If I change one of the values in the Threshold column to text, the entire column is now text.

common data types

One useful feature of the table constructor is that the values in the table can be the result of any DAX expression that returns a scalar value.

You can refer to measures, or columns in other tables and use those to construct the table.

referring to dax expressions

DATATABLE Function in DAX

The syntax for DATATABLE is similar to #table in Power Query

datatable function syntax

1 Give the new table a name

2 Call the DATATABLE function

3 The column names

4 The data types of the columns

5 The data for the columns

To use this, on the PBI Desktop Ribbon click on New table (on the Modelling tab) and enter the data

new datatable

The columns are already named and the columns have a data type, so that saves us a few steps compared to using the table constructor.

Conclusion

Static tables can be handy when you don't want to load data that never (or rarely) changes.

I've shown you 5 different ways to create static tables in Power Query and DAX. I hope these approaches are useful to you.

static data tables

More Power BI Posts

custom tooltips in power bi

Custom Tooltips in Power BI

Create custom tooltips for Power BI visuals. Use charts, images, numeric or categorical data to enhance the information your visuals convey
conditional formatting tables and matrices in power bi

Conditional Formatting in Power BI Tables and Matrices

How to apply conditional formatting to tables and matrices in Power BI. Use color, icons, data bars and URL's.
dax editor keyboard shortcuts

DAX Editor Keyboard Shortcuts

Keyboard shortcuts for the DAX editor in Power BI. Learning DAX is hard enough. Make your life easier by using these keyboard shortcuts.
fixing incorrect totals in dax

Fixing Incorrect Totals in DAX

If you're getting incorrect totals in your DAX measures, this post explains how to fix them. VIDEO and Sample file to download.
highlighting data in power bi visuals

Highlighting Data in Power BI Visuals

Learn several techniques to highlight or label important data points in your Power BI visuals. Sample file and code to download.
Power BI Organizational Data Types

Power BI Organizational Data Types in Excel

Power BI Organizational Data Types in Excel revolutionize the way we store and access our data with one central location and small files.
shape maps in power bi

Shape Maps in Power BI

Shape maps in Power BI can be used to show the distribution of a variable across geographic regions. Learn a trick to plot discrete data too.

Converting Decimal Time to Days, Hours, Minutes, Seconds in Power BI

Convert times and durations from decimal numbers to easily understood formats like hh:mm:ss. Sample code and file to download.
Power BI Dashboards

How to Build Power BI Dashboards

This video tutorial takes you step by step through building Power BI dashboards and reports, including publishing and sharing.

More Power Pivot Posts

custom tooltips in power bi

Custom Tooltips in Power BI

Create custom tooltips for Power BI visuals. Use charts, images, numeric or categorical data to enhance the information your visuals convey
conditional formatting tables and matrices in power bi

Conditional Formatting in Power BI Tables and Matrices

How to apply conditional formatting to tables and matrices in Power BI. Use color, icons, data bars and URL's.
dax editor keyboard shortcuts

DAX Editor Keyboard Shortcuts

Keyboard shortcuts for the DAX editor in Power BI. Learning DAX is hard enough. Make your life easier by using these keyboard shortcuts.
CUBE Functions

Excel CUBE Functions

Excel CUBE Functions are a great alternative to GETPIVOTDATA for Power Pivot PivotTables and still work with Slicers.
fixing incorrect totals in dax

Fixing Incorrect Totals in DAX

If you're getting incorrect totals in your DAX measures, this post explains how to fix them. VIDEO and Sample file to download.
Introduction to DAX

Introduction to DAX Measures

Introduction to DAX in Power BI and Excel. Where and how to write DAX measures, understanding filter context and more.

Toggle Top N with Slicers

Toggle Top N with Slicers using disconnected tables in Power Pivot. These easy measures allow users to choose their top n.
power pivot running total

Power Pivot Running Total

The defaul Power Pivot Running Total setting doesn't handle aggregated periods. This measure solves this issue.
highlighting data in power bi visuals

Highlighting Data in Power BI Visuals

Learn several techniques to highlight or label important data points in your Power BI visuals. Sample file and code to download.
Power BI Organizational Data Types

Power BI Organizational Data Types in Excel

Power BI Organizational Data Types in Excel revolutionize the way we store and access our data with one central location and small files.

More Power Query Posts

Power Query if Statements incl. Nested ifs, if or, if and

How to write Power Query if statements, including nested if, ‘if or’ and ‘if and’, which are easier to write than their Excel counterparts.
power query variables

Power Query Variables 3 Ways

Power Query Variables enable you to create parameters that can be used repeatedly and they’re easily updated as they’re stored in one place.
custom tooltips in power bi

Custom Tooltips in Power BI

Create custom tooltips for Power BI visuals. Use charts, images, numeric or categorical data to enhance the information your visuals convey
conditional formatting tables and matrices in power bi

Conditional Formatting in Power BI Tables and Matrices

How to apply conditional formatting to tables and matrices in Power BI. Use color, icons, data bars and URL's.
delete empty rows and columns using power query

Remove Blank Rows and Columns from Tables in Power Query

Delete blank rows and columns from tables using Power Query. Even rows/columns with spaces, empty strings or non-printing whitespace
extracting data from lists and records in power query

Extracting Data from Nested Lists and Records in Power Query

Learn how to extract data from lists and records in Power Query, including examples where these data structures are nested inside each other.
dax editor keyboard shortcuts

DAX Editor Keyboard Shortcuts

Keyboard shortcuts for the DAX editor in Power BI. Learning DAX is hard enough. Make your life easier by using these keyboard shortcuts.
combine files with different column names in power query

Combine Files With Different Column Names in Power Query

Learn how to load data into Power Query when the column names in your data don't match up. Sampe files to download.
CUBE Functions

Excel CUBE Functions

Excel CUBE Functions are a great alternative to GETPIVOTDATA for Power Pivot PivotTables and still work with Slicers.
fixing incorrect totals in dax

Fixing Incorrect Totals in DAX

If you're getting incorrect totals in your DAX measures, this post explains how to fix them. VIDEO and Sample file to download.


Category: Power BI, Power Pivot, Power Query
Previous Post:Power Query change type using localeChange Type Using Locale with Power Query
Next Post:Excel Workbook ProtectionWorkbook Protection

Reader Interactions

Comments

  1. THIERRY SOUCHARD

    April 2, 2021 at 12:49 am

    Great read, thanks. I just happened to need it today!

    One thing that made me struggle though: #”Column Name” = Text.Type is the correct syntax for text and not the usual “type text” as found elsewhere in M.

    Reply
    • Philip Treacy

      April 8, 2021 at 3:41 pm

      Yes Thierry, I used similar syntax in my examples for Int and Number (decimal).

      Regards

      Phil

      Reply
  2. Jim Fitch

    March 31, 2021 at 11:39 pm

    These are great tips! We frequently encounter such static or semi-static situations. My bias generally would be to embed the values in a table using 1 of these techniques. A key decision, of course, is whether or not those situations exist across multiple applications where it would be better to have a common look-up/reference than to embed the data in multiple applications/workbooks. But, let’s hear it for having the option! Thanks for the tips!

    Reply
    • Philip Treacy

      April 1, 2021 at 3:32 pm

      No worries Jim, glad it was helpful.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.