Code Generator | M (Power Query)
Creating a Table in M (Power Query)
Learn how to create a table in M (Power Query) by following a step-by-step process. The process involves defining a function, validating inputs, creating column definitions and data rows, and combining them into a table. Example code and usage are
![Empty image or helper icon](https://fs.enterprisedna.co/library/data-mentor/cover-images/5xhCzovFm17MWAJPPeaJ6xTneoYZdbWA8qccEoeZ.webp)
Prompt
Answer
To create a table in M (Power Query), you can follow these steps:
Define the function:
- The function will take a list of column names and a list of data rows as parameters.
- It will return a table.
Validate the inputs:
- Ensure that the list of column names is not empty.
- Ensure that the list of data rows is not empty.
- Ensure that all data rows have the same number of columns as the list of column names.
Create the column definitions:
- Iterate over the list of column names and create a record for each column definition using the
#table
function.
- Iterate over the list of column names and create a record for each column definition using the
Create the rows of data:
- Iterate over the list of data rows and create records using the
Record.FromList
function. Each record will have field names corresponding to the column names.
- Iterate over the list of data rows and create records using the
Combine the column definitions and data rows:
- Use the
Table.Combine
function to combine the column definitions and data rows into a single table.
- Use the
Here's the code that implements these steps:
let
CreateTable = (columnNames as list, dataRows as list) as table =>
let
// Step 2: Validate inputs
columnCount = List.Count(columnNames),
rowCount = List.Count(dataRows),
validInput = columnCount > 0
and rowCount > 0
and List.Count(List.First(dataRows)) = columnCount,
// Step 3: Create column definitions
columnDefinitions = List.Transform(columnNames, each #table(1, {{_}})),
// Step 4: Create data rows
tableRows = List.Transform(dataRows, each Record.FromList(_, columnNames)),
// Step 5: Combine column definitions and data rows
result = if validInput then
Table.Combine({columnDefinitions, tableRows})
else
error "Invalid input"
in
result
in
CreateTable
Example usage:
let
columnNames = {"Name", "Age", "City"},
dataRows = {{"John", 30, "New York"}, {"Jane", 25, "London"}},
table = CreateTable(columnNames, dataRows)
in
table
Output:
| Name | Age | City |
|------|-----|----------|
| John | 30 | New York |
| Jane | 25 | London |
This code defines a function called CreateTable
that takes a list of column names and a list of data rows as parameters. It performs validation, creates the column definitions and data rows, and combines them into a table. The function can be used to create a table by passing the column names and data rows as arguments.
Description
Learn how to create a table in M (Power Query) by following a step-by-step process. The process involves defining a function, validating inputs, creating column definitions and data rows, and combining them into a table. Example code and usage are provided to demonstrate the implementation.
More Code Generators
![Apache Flink Code Generator](https://fs.enterprisedna.co/library/icons/flink.png)
![Apache Pig Code Generator](https://fs.enterprisedna.co/library/icons/apache_pig.png)
![Azure Data Factory Code Generator](https://fs.enterprisedna.co/library/icons/azure-data-factory.png)
![C/C++ Code Generator](https://fs.enterprisedna.co/library/icons/c_c_plus.png)
![CouchDB Code Generator](https://fs.enterprisedna.co/library/icons/couchdb.png)
![DAX Code Generator](https://fs.enterprisedna.co/library/icons/power-bi.png)
![Excel Code Generator](https://fs.enterprisedna.co/library/icons/excel.png)
![Firebase Code Generator](https://fs.enterprisedna.co/library/icons/firebase.png)
![Google BigQuery Code Generator](https://fs.enterprisedna.co/library/icons/google_bigquery.png)
![Google Sheets Code Generator](https://fs.enterprisedna.co/library/icons/google_sheets.png)
![GraphQL Code Generator](https://fs.enterprisedna.co/library/icons/graphql.png)
![Hive Code Generator](https://fs.enterprisedna.co/library/icons/hive.png)
![Java Code Generator](https://fs.enterprisedna.co/library/icons/java.png)
![JavaScript Code Generator](https://fs.enterprisedna.co/library/icons/javascript.png)
![Julia Code Generator](https://fs.enterprisedna.co/library/icons/julia.png)
![Lua Code Generator](https://fs.enterprisedna.co/library/icons/lua.png)
![M (Power Query) Code Generator](https://fs.enterprisedna.co/library/icons/m.png)
![MATLAB Code Generator](https://fs.enterprisedna.co/library/icons/matlab.png)
![MongoDB Code Generator](https://fs.enterprisedna.co/library/icons/mongodb.png)
![Oracle Code Generator](https://fs.enterprisedna.co/library/icons/oracle.png)
![PostgreSQL Code Generator](https://fs.enterprisedna.co/library/icons/postgresql.png)
![Power BI Code Generator](https://fs.enterprisedna.co/library/icons/power-bi.png)
![Python Code Generator](https://fs.enterprisedna.co/library/icons/python.png)
![R Code Generator](https://fs.enterprisedna.co/library/icons/r.png)
![Redis Code Generator](https://fs.enterprisedna.co/library/icons/redis.png)
![Regex Code Generator](https://fs.enterprisedna.co/library/icons/regex.png)
![Ruby Code Generator](https://fs.enterprisedna.co/library/icons/ruby.png)
![SAS Code Generator](https://fs.enterprisedna.co/library/icons/sas.png)
![Scala Code Generator](https://fs.enterprisedna.co/library/icons/scala.png)
![Shell Code Generator](https://fs.enterprisedna.co/library/icons/shell.png)
![SPSS Code Generator](https://fs.enterprisedna.co/library/icons/spss.png)
![SQL Code Generator](https://fs.enterprisedna.co/library/icons/sql.png)
![SQLite Code Generator](https://fs.enterprisedna.co/library/icons/sqlite.png)
![Stata Code Generator](https://fs.enterprisedna.co/library/icons/stata.png)
![Tableau Code Generator](https://fs.enterprisedna.co/library/icons/tableau.png)
![VBA Code Generator](https://fs.enterprisedna.co/library/icons/vba.png)