Skip to main content
HomeCheat sheetsPower BI

Working with Tables in Power Query M in Power BI

Learn how to work with tables in Power Query M in Power BI in this convenient cheat sheet!
Jul 2023  · 6 min read

Have this cheat sheet at your fingertips

Download PDF

Records

Creation

// Define a record with [name = value]
//You can include different data types including lists
[
  Name = "William Playfair",
  BirthYear = 1759,
  IsDataScientist = true, 
  ChartsInvented = {"line", "bar", "area", "pie"}
]

// Create a record from lists of values and fields with FromList()
Record.FromList(
  {"William Playfair", 1759, true, {"line", "bar", "area", "pie"}},
  {"Name", "BirthYear", "IsDataScientist", "ChartsInvented"}
)
// Create a record from a table of records with FromTable()
Record.FromTable(
  Table.FromRecords({
    [Name = "Name", Value = "William Playfair"],
    [Name = "BirthYear", Value = 1759],
    [Name = "IsDataScientist", Value = true],
    [Name = "ChartsInvented", Value = {"line", "bar", "area", "pie"}]
  })
) 

// Records can be nested 
[
  Name = [First = "William", Last = Playfair"],
  BirthYear = 1759,
  IsDataScientist = true, 
  ChartsInvented = {"line", "bar", "area", "pie"}
]

Example records

// Define a record
let
  TaylorSwift = [
    FirstName = "Taylor", 
    LastName = "Swift", 
    BirthDate = #date(1989, 12, 13)
  ] 
in
  TaylorSwift

Counting

// Get the number of fields with FieldCount()
Record.FieldCount(TaylorSwift) // Returns 3

// Determine if a record has a field name with HasFields()
Record.HasFields(TaylorSwift, "LastName") // Returns true

Selection

// Access a field with square brackets
TaylorSwift[FirstName] // Returns "Taylor"

// Dynamically access a field with Field()
Record.Field(TaylorSwift, "FirstName")

// Provide a deafult if the field isn't found with FieldOrDefault()
Record.FieldOrDefault(TaylorSwift, "Address", "N/A") // Returns "N/A"

// Get a subset of fields with SelectFields()
Record.SelectFields(TaylorSwift, {"FirstName", "LastName"}) 
  // Returns [FirstName = "Taylor", LastName = "Swift"]

// Get all the field names with FieldNames()
Record.FieldNames(TaylorSwift) // Returns {"FirstName", "LastName", "BirthDate"}

// Get all the field values with FieldValues()
Record.FieldValues(TaylorSwift) // Returns {"Taylor", "Swift", #date(1989, 12, 13)}

Manipulation/Transformation

// Add a new field with AddField()
Record.AddField(TaylorSwift, "MiddleName", "Alison")

// Combine fields from records with Combine()
Record.Combine(TaylorSwift)

// Remove fields with RemoveFields()
Record.RemoveFields(TaylorSwift)

// Change the order of fields with ReorderFields()
Record.ReorderFields(TaylorSwift)

// Change values in a field with TransformFields()
Record.TransformFields(TaylorSwift, {"BirthDate", Date.ToText})

Metadata

// Add a metadata record to a value with meta
"To a collector of curios, the dust is metadata." meta [
  ContentType = "quote",
  Author = "David Weinberger",
  Source = "Everything Is Miscellaneous: The Power of the New Digital Disorder"
]

// Remove all metadata with RemoveMetadata()
Value.RemoveMetadata(curios)

// Remove specific metadata with RemoveMetadata(, metaValue)
Value.RemoveMetadata(curios, "Author")

Tables

Creation

// Create a table with #table()
#table(
  {"Name", "BirthYear", "IsDataScientist", "ChartsInvented"},
  {
    {"William Playfair", 1759, true, {"line", "bar", "area", "pie"}},
    {"Karl Pearson", 1857, true, {"histogram"}}
  }
)

// Create a table from a list of records with FromRecords()
Table.FromRecords({
  [
    Name = "William Playfair",
    BirthYear = 1759,
    IsDataScientist = true, 
    ChartsInvented = {"line", "bar", "area", "pie"}
  ],
  [
    Name = "Karl Pearson",
    BirthYear = 1857,
    IsDataScientist = true, 
    ChartsInvented = {"histogram"}
  ]}
)

// Enforce column data types with type table[]
Table.FromRecords({
  [
    Name = "William Playfair",
    BirthYear = 1759,
    IsDataScientist = true, 
    ChartsInvented = {"line", "bar", "area", "pie"}
  ],
  [
    Name = "Karl Pearson",
    BirthYear = 1857,
    IsDataScientist = true, 
    ChartsInvented = {"histogram"}
  ]},
  type table[Name = text, BirthYear = number, IsDataScientist = logical, ChartsInvented = list]
)

// Create a table from a list of lists with FromColumns()
Table.FromColumns({
	{"William Playfair", "Karl Pearson"},
	{1759, 1857},
	{true, true},
	{{"line", "bar", "area", "pie"}, {"histogram"}}
  },
  {"Name", "BirthYear", "IsDataScientist ", "ChartsInvented"}
)

// Create a table from a list of lists with FromRows()
Table.FromRows({
	{"William Playfair", 1759, true, {"line", "bar", "area", "pie"}},
	{"Karl Pearson", 1857, true, {"histogram"}}
  },
  {"Name", "BirthYear", "IsDataScientist ", "ChartsInvented"}
)

Example tables

// Define tables
let
  Musicians = #table(
    {"ID", "FirstName", "LastName", "BirthDate"},
    {
      {1, "Taylor", "Swift", #date(1989, 12, 13)},
      {2, "Ed", "Sheeran", #date(1991, 2, 17)}
    }
  )
in
  Musicians

let
  Albums = #table(
    {"ID", "ArtistID", "Title"},
    {
      {1, 1, "1989"},
      {2, 2, "-"},
      {3, 2, "5"}
    }
)
in
  Albums

Counting

// Get the number of rows with RowCount()
Table.RowCount(Musicians) // Returns 2

// Get the number of columns with ColumnCount()
Table.ColumnCount(Musicians) // Returns 4

// Get the column names with ColumnNames()
Table.ColumnNames(Musicians) // Returns {"ID", "FirstName", "LastName", "BirthDate"}

// Get details of the columns with Schema()
Table.Schema(Musicians) // Returns a table of column details

// Get a summary of number columns with Profile()
Table.Profile(Musicians) // Returns a table of min, max, mean, etc. by column

Selection

// Get a record by position with {}
Musicians{0} // Returns Taylor Swift record

// Get a column with []
Musicians[FirstName]

// Get a column dynamically with Column()
Table.Column(Musicians, "FirstName")

// Get the first few rows with FirstN()
Table.FirstN(Musicians, 1) // Returns first record

// Get the last few element with LastN()
Table.LastN(Musicians, 1) // Returns last record

// Select unique records with Distinct()
Table.Distinct(Table.Combine(Musicians, Musicians)) // Returns Musicians

// Get elements that match a criteria with SelectRows()
Table.SelectRows(Musicians, each Text.Contains([FirstName], "Tay")) // Returns the Taylor Swift record

// Return true if all elements match a criteria with MatchesAllRows()
Table.MatchesAllRows(Musicians, each [IsDataScientist]) // Returns true

// Return true if any elements match a criteria with MatchesAnyRows()
Table.MatchesAnyRows(Musicians, each Text.Contains([FirstName], "Drake")) // Returns false

Row manipulation

// Insert records into a table with InsertRows()
Table.InsertRows(
  Musicians,
  1,
  {[FirstName = "Bad", LastName = "Bunny", BirthDate = #date(1994, 3, 10)]}
) // Returns a table with new record after previous 1st record

// Vertically concatenate tables with Combine()
Table.Combine(
  Musicians, 
  Table.FromRecords({
    [FirstName = "Bad", LastName = "Bunny", BirthDate = #date(1994, 3, 10)]
  })
) // Returns a table with 3 records

// Remove records with RemoveRows()
Table.RemoveRows(Musicians, 0) // Returns table without 0th record

// Change the order of records with Sort()
Table.Sort(Musicians, {"FirstName"}) // Returns by alphabetical order of FirstName

// Change values in a field with TransformRows()
Table.TransformRows(Musicians, {"BirthDate", Date.ToText})

// Calculate grouped aggregations with Group()
Table.Group(Musicians, "FirstName", each List.Min([BirthDate])

Column manipulation

// Add a column to a table with AddColumn()
Table.AddColumn(Musicians, "FullName", each [FirstName] & [LastName]) // Returns table with extra column

// Select columns of a table with SelectColumns()
Table.SelectColumns(Musicians, {"FirstName", "LastName"}) // Returns 2 columns

// Drop columns of a table with RemoveColumns()
Table.RemoveColumns(Musicians, {"BirthDate"}) // Returns remaining 3 columns

// Change the order of columns with ReorderColumns()
Table.ReorderColumns(Musicians, {"LastName", "FirstName", "BirthDate", "ID"}) // Returns table with new column order

// Change values in a field with TransformColumns()
Table.TransformColumns(
  Musicians, 
  {{"FirstName", Text.Upper}, {"LastName", Text.Lower}}
)

Table Relations

// Set as column as the primary key with AddKey(, , true)
Table.AddKey(Musicians, "ID", true)

// Set as column as the secondary key with AddKey(, , false)
Table.AddKey(Albums, "ArtistID", false)

// Join two tables with Join()
Table.Join(Musicians, "ID", Albums, "ArtistID", JoinKind.LeftOuter)

Pivoting

// Convert from wide to long with Unpivot()
Table.Unpivot(Musicians, {"FirstName", "LastName"}, "NameType", "NameValue") // Returns table with FirstName and LastName on their own rows

// Convert from long to wide with Pivot()
Table.Unpivot(MusiciansLong, {"FirstName", "LastName"}, "NameType", "NameValue") // Reverses the unpivot step
Topics
Related

Top 10 Business Intelligence Conferences in 2024

An overview of the top 10 international business intelligence conferences planned for 2024, including their dates, participation fees, formats, hosting cities, main focus, key discussion topics, and target audience.
Elena Kosourova's photo

Elena Kosourova

5 min

Becoming Remarkable with Guy Kawasaki, Author and Chief Evangelist at Canva

Richie and Guy explore the concept of being remarkable, growth, grit and grace, the importance of experiential learning, imposter syndrome, finding your passion, how to network and find remarkable people, measuring success through benevolent impact and much more. 
Richie Cotton's photo

Richie Cotton

55 min

Mastering SWITCH in DAX for Power BI: A Comprehensive Guide

Explore the Power BI SWITCH function, what it is, how to use it, and some common pitfalls and best practices.
Joleen Bothma's photo

Joleen Bothma

10 min

A Step-By-Step Guide to Visualizing KPIs in Power BI

Learn how to effectively visualize KPIs in Power BI using visuals and DAX measures.
Joleen Bothma's photo

Joleen Bothma

8 min

Power BI Slicer Tutorial: A Comprehensive Guide to Enhancing Your Reports

Unlock the power of Power BI slicers with our step-by-step tutorial. Learn to create, customize, and optimize slicers for better reports.
Joleen Bothma's photo

Joleen Bothma

7 min

Complete Guide to Power BI Conditional Formatting

Learn how to use conditional formatting to boost your Power BI reports.
Joleen Bothma's photo

Joleen Bothma

7 min

See MoreSee More