Row manipulation
> Tables
Working With Tables in
// Insert records into a table with InsertRows()
Creation Table.InsertRows(
Musicians,
Power Query M in Power BI // Create a table with #table()
1,
#table(
{[FirstName = "Bad", LastName = "Bunny", BirthDate = #date(1994, 3, 10)]}
{"Name", "BirthYear", "IsDataScientist", "ChartsInvented"},
) // Returns a table with new record after previous 1st record
{"William Playfair", 1759, true, {"line", "bar", "area", "pie"}},
{"Karl Pearson", 1857, true, {"histogram"}}
// Vertically concatenate tables with Combine()
}
Table.Combine(
Musicians,
Learn Power BI online at www.DataCamp.com // Create a table from a list of records with FromRecords()
Table.FromRecords({
[FirstName = "Bad", LastName = "Bunny", BirthDate = #date(1994, 3, 10)]
Table.FromRecords({
[
})
Name = "William Playfair",
) // Returns a table with 3 records
BirthYear = 1759,
IsDataScientist = true,
// Remove records with RemoveRows()
>
ChartsInvented = {"line", "bar", "area", "pie"}
Records ],
Table.RemoveRows(Musicians, 0) // Returns table without 0th record
Name = "Karl Pearson",
// Change the order of records with Sort()
Creation BirthYear = 1857,
IsDataScientist = true,
Table.Sort(Musicians, {"FirstName"}) // Returns by alphabetical order of FirstName
ChartsInvented = {"histogram"}
// Define a record with [name = value]
]}
// Change values in a field with TransformRows()
//You can include different data types including lists
)
Table.TransformRows(Musicians, {"BirthDate", Date.ToText})
Name = "William Playfair",
// Enforce column data types with type table[]
Table.FromRecords({
// Calculate grouped aggregations with Group()
BirthYear = 1759,
[
Table.Group(Musicians, "FirstName", each List.Min([BirthDate])
IsDataScientist = true,
Name = "William Playfair",
ChartsInvented = {"line", "bar", "area", "pie"}
BirthYear = 1759,
IsDataScientist = true,
Column manipulation
ChartsInvented = {"line", "bar", "area", "pie"}
// Create a record from lists of values and fields with FromList()
],
Record.FromList(
// Add a column to a table with AddColumn()
// Change the order of columns with ReorderColumns()
{"William Playfair", 1759, true, {"line", "bar", "area", "pie"}},
Name = "Karl Pearson",
Table.AddColumn(Musicians, "FullName", each Table.ReorderColumns(Musicians, {"LastName",
{"Name", "BirthYear", "IsDataScientist", "ChartsInvented"}
BirthYear = 1857,
[FirstName] & [LastName]) // Returns table "FirstName", "BirthDate", "ID"}) // Returns table
)
IsDataScientist = true,
with extra column
with new column order
// Create a record from a table of records with FromTable()
ChartsInvented = {"histogram"}
Record.FromTable(
]},
type table[Name = text, BirthYear = number, IsDataScientist = logical, ChartsInvented = list]
// Select columns of a table with // Change values in a field with TransformColumns()
Table.FromRecords({
[Name = "Name", Value = "William Playfair"],
)
SelectColumns()
Table.TransformColumns(
[Name = "BirthYear", Value = 1759],
Table.SelectColumns(Musicians, Musicians,
// Create a table from a list of lists with FromColumns()
{"FirstName", "LastName"}) // Returns 2 {{"FirstName", Text.Upper}, {"LastName",
[Name = "IsDataScientist", Value = true],
Table.FromColumns({
[Name = "ChartsInvented", Value = {"line", "bar", "area", "pie"}]
{"William Playfair", "Karl Pearson"},
columns
Text.Lower}}
})
{1759, 1857},
)
)
{true, true},
// Drop columns of a table with
{{"line", "bar", "area", "pie"}, {"histogram"}}
RemoveColumns()
// Records can be nested
},
[
Table.RemoveColumns(Musicians,
{"Name", "BirthYear", "IsDataScientist ", "ChartsInvented"}
Name = [First = "William", Last = Playfair"],
)
{"BirthDate"}) // Returns remaining 3
BirthYear = 1759,
columns
IsDataScientist = true,
// Create a table from a list of lists with FromRows()
ChartsInvented = {"line", "bar", "area", "pie"}
Table.FromRows({
]
{"William Playfair", 1759, true, {"line", "bar", "area", "pie"}},
Table Relations
{"Karl Pearson", 1857, true, {"histogram"}}
},
{"Name", "BirthYear", "IsDataScientist ", "ChartsInvented"}
// Set as column as the primary key with AddKey(, , true)
) Table.AddKey(Musicians, "ID", true)
Example records
// Set as column as the secondary key with AddKey(, , false)
// Define a record
Example tables Table.AddKey(Albums, "ArtistID", false)
let
TaylorSwift = [
// Define tables
// Join two tables with Join()
FirstName = "Taylor",
let
Table.Join(Musicians, "ID", Albums, "ArtistID", JoinKind.LeftOuter)
LastName = "Swift",
Musicians = #table(
BirthDate = #date(1989, 12, 13)
{"ID", "FirstName", "LastName", "BirthDate"},
]
{
Pivoting
in
{1, "Taylor", "Swift", #date(1989, 12, 13)},
TaylorSwift {2, "Ed", "Sheeran", #date(1991, 2, 17)}
// Convert from wide to long with Unpivot()
)
Table.Unpivot(Musicians, {"FirstName", "LastName"}, "NameType", "NameValue") // Returns table
Counting in
with FirstName and LastName on their own rows
Musicians
// Get the number of fields with FieldCount()
// Convert from long to wide with Pivot()
let
Record.FieldCount(TaylorSwift) // Returns 3
Albums = #table(
Table.Unpivot(MusiciansLong, {"FirstName", "LastName"}, "NameType", "NameValue") // Reverses
{"ID", "ArtistID", "Title"},
the unpivot step
// Determine if a record has a field name with HasFields()
{
Record.HasFields(TaylorSwift, "LastName") // Returns true {1, 1, "1989"},
{2, 2, "-"},
{3, 2, "5"}
Manipulation/Transformation }
in
// Add a new field with AddField()
Albums
Record.AddField(TaylorSwift, "MiddleName", "Alison")
// Combine fields from records with Combine()
Record.Combine(TaylorSwift)
Counting
// Remove fields with RemoveFields()
// Get the number of rows with RowCount()
Record.RemoveFields(TaylorSwift)
Table.RowCount(Musicians) // Returns 2
// Change the order of fields with ReorderFields()
// Get the number of columns with ColumnCount()
Record.ReorderFields(TaylorSwift)
Table.ColumnCount(Musicians) // Returns 4
// Change values in a field with TransformFields()
// Get the column names with ColumnNames()
Record.TransformFields(TaylorSwift, {"BirthDate", Date.ToText}) Table.ColumnNames(Musicians) // Returns {"ID", "FirstName", "LastName", "BirthDate"}
// Get details of the columns with Schema()
Table.Schema(Musicians) // Returns a table of column details
Learn Power BI Online at
Metadata
// Get a summary of number columns with Profile()
www.DataCamp.com
Table.Profile(Musicians) // Returns a table of min, max, mean, etc. by column
// Add a metadata record to a value with meta
"To a collector of curios, the dust is metadata." meta [
ContentType = "quote",
Author = "David Weinberger",
Selection
Source = "Everything Is Miscellaneous: The Power of the New Digital Disorder"
// Get a record by position with {}
// Select unique records with Distinct()
Musicians{0} // Returns Taylor Swift Table.Distinct(Table.Combine(Musicians, Musicians)) //
// Remove all metadata with RemoveMetadata()
record
Returns Musicians
Value.RemoveMetadata(curios)
// Get a column with []
// Get elements that match a criteria with
// Remove specific metadata with RemoveMetadata(, metaValue)
Musicians[FirstName]
SelectRows()
Value.RemoveMetadata(curios, "Author") Table.SelectRows(Musicians, each
// Get a column dynamically with Text.Contains([FirstName], "Tay")) // Returns the
Column()
Taylor Swift record
Table.Column(Musicians, "FirstName")
// Return true if all elements match a criteria with
// Get the first few rows with FirstN()
MatchesAllRows()
Table.FirstN(Musicians, 1) // Returns Table.MatchesAllRows(Musicians, each
first record
[IsDataScientist]) // Returns true
// Get the last few element with LastN()
// Return true if any elements match a criteria with
Table.LastN(Musicians, 1) // Returns MatchesAnyRows()
last record Table.MatchesAnyRows(Musicians, each
Text.Contains([FirstName], "Drake")) // Returns false
