Create table using Power Query in Power BI [With 31 real examples]

In this Power BI Tutorial, we will discuss how to create table using Power Query in Power BI. And also we will discuss the below examples:

• Power BI how to use power query to create a New table
• Power BI how to use blank query
• Power BI how to use power query add a column from another table
• Power BI how to use power query remove duplicates
• Power BI how to use power query add a column
• Power BI how to use power query add column if statement
• Power BI how to use power query based on condition
• Power BI how to use power query and operator
• Power BI how to use power query append tables
• Power BI how to use power query reference another table
• Power BI how to use power query by multiple columns
• Power BI how to use power query add row
• Power BI how to use power query combine columns
• Power BI how to use power query calendar table
• Power BI how to use power query lookup value from another table
• Power BI how to use power query hide column
• Power BI how to use power query join table
• Power BI how to use power query join on multiple columns
• Power BI how to use power query union tables
• Power BI how to use power query unpivot
• Power BI how to use power query remove rows based on the value
• Power BI how to use power query replace values
• Power BI how to use power query sort multiple columns
• Power BI how to use power query sum column
• Power BI how to use power query select column
• Power BI how to use power query count rows
• Power BI how to use power query not equal
• Power BI how to use power query not null
• Power BI how to use power query replace null
• Power BI how to use power query get the previous row
• Power BI how to use power query remove null rows

If you are a beginner in Power BI, check out What is Microsoft Power BI and a video tutorial on Power BI.

Create a table in Power Query

Here we will see how to create a new table using Power Query in Power BI.

We will use Enter data option to enter data directly into the Power Query data, then we can load the data into Power BI.

• In the Power Query editor, click on the Enter data from the ribbon.
• Once you click on Enter data option, Create Table window will open.
• Here you can enter your own column name and row value based on your requirement. In this case, I have created three columns i.e. Product name, order date, and Price column.
• Then you can rename the table from Table to Product.
• Once the table gets created, the Power query editor by default will recognize the data type, or else you can change the data type from the Transform section in the Power query editor.

Now you can see the table get created directly inside the Power query editor.

This is an example of how to create a table in Power Query in Microsoft Power BI.

Read Built-in Date Hierarchy in Power BI and Power Query Examples

Power BI how to use blank query

Here we will see how to use the blank query to create a new table in Microsoft Power BI.

The Blank query in the Power query editor gives a space where you can query a new query, which means you will write the queries from scratch to create a new query or table.

The syntax is to create columns and add row values using the blank query in the Power query editor.

 #table(
    {
      "Name",   // First Column Name
      "Age"   // Second Column Name
    }, 
    {
      {
        Alex,28     // First Row Field Values
      },
      {
        Hussain,46     // Second Row Field Values
      }
    }
  )

We will create a custom table using Blank query in Power Query Editor.

• In Power Query Editor, click on the New source-> select Blank Query.

• In the formula, the first part of the table function is the name of the column, and the second part is the row value.
• Then write the query in the Formula bar, to create the table having two columns i.e. Name and Department with row value provide the below formula. Press enter or click on the tick symbol.
• Then you can change the data type from the Data type option present in the ribbon.

= #table({"Name","Department"},{{"Alex","IT"},{"John","Finance"},{"Ruby","IT"}})

It will create a custom table with two columns in the Power Query table.

This is an example of Power BI how to use a blank query in the Power query editor.

Read PowerBI Report Filter using Slicer Visualization

Power BI how to use Power query to add a column

Here we will see how to add a column from an example by using Power Query Editor

So here we will use the below table to add a column from an example by using Power Query Editor.

We will add a new column by providing more than one sample value to create it. It can be done by providing the input-based selected column. In this case, the selected column is Rank Name.

• In Power Query Editor, go to Add column-> select Column From Example-> From selection from the ribbon.

• Now rename Column 1 to Rank and provide a value from the selected column. In this case, it is “1.”, query editor will show the rank i.e., 1., 2., and so on. And the transformation steps can see in the Data Preview. Then click on OK.

Now you can see in the data table, we have added a rank column.

This is an example of how to use a Power query to add the column in Power BI.

Read How to Create Power bi report from SharePoint Online list

Power query add a column from another table

Here we will see in Power BI how to use Power Query to add a column from another table. To add a column from another table we will use the Custom column in Power Query Editor.

So here we have two tables, one is the Emp table having column Name and Department, and another in Date table having a single column i.e DOB.

Emp table

Date table

Next, we will add the Name column from the employee table to the Date table. Follow the steps below:

• In Power Query Editor, go to Add column tab-> Custom column from the ribbon.

• Then Custom column window will open, in the formula box write the below formula to add the name column to the data table. Then click on Ok.

#"Emp table"[Name]

• Now click on Expand icon in the Name column-> click on Expand to New Row from the options.

Now you can see the Name column added to the Date table using a custom column in the Power query editor.

This is an example of how to use a power query to add a column from another table in Power BI.

Read Power BI Query Group Tutorial

Power BI how to use power query remove duplicates

Here we will see how to remove duplicates from columns using the query editor in Power BI.

We will remove the duplicated value from the table or column, using Power Query Editor in Power BI.

• In Power Query Editor, go to the Home tab-> Remove rows-> Remove Duplicates from the ribbon.

• Now you can see all the duplicated values are removed from the table.

This is an example of how to use the Power query to remove duplicates in Power BI.

Read Power BI Change Data Type of a Column

Power BI how to use power query add column if statement

Here we will see how to add a custom column by using the If statement in the query editor.

For this example, we will use the below table, and then we will add the column for the Age category. If the age is greater than 18 then Adult, and if less than 18 then children.

• In Power Query Editor, go to Add column-> Conditional column from the ribbon.

• Now Conditional column window will open, Then provide the name to the column as Category and provides the If part Age is greater than 18, then Adult, else child.
• If you want to add the If clause then clicks on the Add clause icon.

Now you can see the column category based on the Age column.

This is an example of how to use power query add column if statement in Power BI.

Read How to Split Columns in Power BI

Power BI how to use power query based on condition

Here we will see how to use a Power query based on a conditional column in Power BI.

So here we have the below table based on the Month number we will get the month name, i.e. if the Month number is 1 then the value is Jan, 2 then the value is Feb.

• In Power QueryEditor, go to Add column-> click on the Conditional column option from the ribbon.

• The next conditional column window will open.
• Then provide the name to the conditional column in the New column name field.
• After that provide the condition, choose the column name as Month Number, operator as equal, number as 1, and provide the value as Jan. Similarly do for other month Feb to Nov and then in Else part write the Dec. Then click on Ok.

• Now you can see the conditional column based on condition is added to the table using the Power Query editor in Power BI.

This is an example of how to use a power query based on conditions in Power BI.

Read Enter Data into Power BI Desktop

Power BI how to use power query and operator

Here we will see how to use AND operator in the Query Editor in Power BI.

For example, if the Age is greater than 16 and less than 40, then we will get True else False. So we will use the custom column to display the result.

• In Power Query Editor, Go to Add column tab-> Custom column from the ribbon.

• Now Custom column window will open, then provide the name of the column. And write the below formula using AND operator. Then click on OK.

[Age]> 16 and [Age]< 40

• Now you can see the result in the custom column, the result can be true or false.

This is an example of how to use power query and operator in Power BI.

Read Power BI Buttons

Power BI how to use power query append tables

Here we will see how to use Power Query to append tables in Power BI.

So, to append two or more tables or queries we will use the Append operation, which creates a new table that has all rows from the first table followed by the second table.

The Append queries operation is based on the column header in both tables but it does not need the relative column position. If the table does not carry a similar column then null values get added to the unsimilar column.

Let’s say we have two tables like below, one is Emp table and Emp table 2, so we will append both tables and create a new table.

Emp table

Emp table 2

• In the Power Query editor, go to the Home tab-> select the Append Queries-> select Append Queries as new.

• Then Append window will open, select the two table option. Then select the first table and then select the second table. Then click on Ok.

• Now you can see the new table by appending both tables using Power Query Editor in Power BI.

This is an example of how to use power query append tables in Power BI.

Read How to use Power BI Workbooks

Power BI how to use power query reference another table

Here we will see how to create a table from another table using the Power Query editor in Power BI.

We will use the reference method to create a table from another table using the Power Query Editor in Power BI. Reference in Power query is used to query a query, which means you will use the result query in some other queries where your primary query remains as a base query.

Let’s say we will create a table from the below table, which will consist of the Name and Age column from the below table.

• In Power Query Editor, in the left side pane under Queries-> select the table or queries-> right-click on it-> click on Reference option.

• Now you can see another table created and has the same data. Now select the column Department and go to Home tab-> Remove columns-> Remove columns.

Now you can see the new table created from another table using the Power Query editor in Power BI.

This is an example of how to use power query reference another table in Power BI.

Read Power BI Error: This content isn’t available

Power BI how to use power query by multiple columns

Here we will see how to pivot two-column into multiple columns using the Power Query editor in Power BI.

We have below tables with 2 columns i.e. Segment and Sale, so here we will pivot the table based on the segment column and the value will be the Sales.

• In Power Query Editor, select the segment column-> go to the Transform tab-> click on the Pivot column option

• Next Pivot column window will open, choose the value column as sales. Then click on Ok.

• Now you can see the table converted into the pivoted table which has multiple columns.

This is an example of how to use a power query by multiple columns in Power BI.

Read Power BI On-premises Data Gateway

Power BI how to use power query add row

Here we will see how to add rows using Advanced editor in Power Query Editor in Microsoft Power BI.

In the below table we will insert a new row in row 1, as in the Power query editor row count start from 0.

To insert a row we will use the Advanced Editor in Power Query Editor.

• In Power Query editor, go to the Home tab-> select the Advanced Editor from the ribbon.

• Then Advance Editor window will open, then write the below queries to insert row.

NewRows =Table.InsertRows( 
    Table.FromRecords({
        [Name = "Alex", Department= "IT", Age =38],
        [Name = "John", Department= "Finance", Age =16]
    }),
    1,
    {[Name = "Bob", Department= "Finance", Age =45]}
)

Then under the in write the NewRows and then click on Done.

Now you can see a row is inserted in position 1 of the table in Power Query Editor.

This is how to add rows using a power query in Power BI.

Read How to share Power BI Dashboard and Report

Power BI how to use power query combine columns

Here we will see how to combine or merge columns using the Power Query editor in Power BI.

In Query editor to merge or combine two more columns, we will use the merge columns option. The column must have a text data type.

For this example, we will use the below table and combine the First name and Last name columns from the table into one column.

• In the Power Query editor, first, we will select the two-column from the table. To select press Ctrl and select the column you want to merge.
• Then go to the Transform tab-> click on the Merge columns from the ribbon.

• You can see the Merge column window will open. Provide the separator, and then give a new column name.

• Now you can see both the columns are merged into one in Power Query Editor.

This is an example of how to use a power query to add rows in Power BI.

Read Microsoft Power BI Error: This content isn’t available

Power BI how to use power query calendar table

Here we will see how to create a calendar table using Power Query Editor in Power BI.

To create a calendar table in Power Query Editor follow the below steps:

• In the Power Query editor, click on the Enter data from the ribbon, to create a calendar table. Then provide the column names as Start Date and then add a start date value, from which date you want to start. Rename the table and click on OK.

• Now we need to add the Last Date column, so go to Add column tab-> select the Custom column option.
• A custom column window will open, then provide the name as the Last date. Next, provide the below formula to get the current date as the last date:

Date.From(DateTime.LocalNow()

• Then change the data type of the Last date to date data type.
• Now we get the start date and last date, now we will create a list of dates between the start date and last date.
• So, go to the Add column tab-> select the Custom column from the ribbon.
• Then the custom column window will open, provide the column name as Date. Then provide the formula below to get the list of dates between the start date and the last date.

{Number.From([Start Date])..Number.From([Last Date])}

• Now we will expand the column to create a new row, so click on the Expand icon in the column->Expand to new rows from the option.

• Now you can see the date from the start date to the last date in the column. Now change the data type of the date column to date.

• Now remove the start date and last date column from the table. Select both the column and then click on remove from the ribbon.
• Next, we will add the year, month, quarter, week, and day column. So, for this right-click on the date column and select the Duplicate column.
• Select the Duplicate column and right-click on it-> Transform-> Year->Year from the option.
• Then rename the column to Year.

• Similarly, you can add other columns after that your calendar table is created.

This is an example of how to use the power query to create a calendar table in Power BI

Read Power BI Custom Visuals

Power BI how to use Power query lookup value from another table

Here we will see how to look up or get value from another table using Power Query Editor in Power BI.

With the help of the List function in the M query i.e. List.PositionOf(), we can get the value, or we will lookup for the value from another table. For Example, we have two sample table

1. Product table: The Product table has three columns these are Product Name, Order Date, and Price.

2. Categories table: This table contains 2 columns Product and Category.

So, we will get the category value into the Product table, for this, we need to create a category index in the product table based on the category in the Categories table. Then we will fetch the category value to the Product table with the help of the Category index.

• In Power Query editor,-> select the Product table-> go to Add column tab-> select the Custom column option.
• Then Custom column window will open. Rename the column as Category index and write the formula in the Formula box. Click on OK

List.PositionOf(Categories[Product],[Product name])

Now you can see the index created in the Product table based on the category column in the Categories table.

• To get the category value, create another custom column and then rename the column as Category. Provide the below formula to get the value:

Categories[Category]{[Category index]}

• Now you can see the category value is added to the Product table.

This is an example of how to use the Power query lookup value from another table in Power BI.

Read Add Web Content to Power BI Dashboard

Power BI how to use power query join table

In Powe query, there are different ways we can join the table, which is available in Merge queries. So we will discuss how to merge the two tables with the help of inner join.

The inner join feature will merge both the tables for the matching rows only. For example, we have two tables these are Product table and the Categories table.

Products table

Categories table

• In Power Query Editor, click on the Merge queries-> Merge Queries from the ribbon.

• Then the Merge queries window will open, select the second table, in this case, it is the Categories table.
• Then select the product name column from the Product table and product from the Categories table.
• Then in the Join Kind section select the Inner (Only matching rows). Click on Ok.

Next, expand the table, so click on the arrow icon and then select the Expand, and unselect the product field, because we already have the product field. Then click on Ok.

Next, you can see both the tables are merged into a single table.

This is an example of how to use a power query to join tables in Power BI

Read Power BI Visualizations

Power BI how to use power query select column

Here we will see how to select multiple columns in the Power query.

In the Power Query editor, first, select a column and then press Ctrl+ click on the columns you want to select.

This is an example of how to use a power query to select columns in Power BI

Power BI how to use power query join on multiple columns

Here we will see how to join or merge multiple columns using the Power Query editor.

We have two tables one is Product 1 and another is Product 2, so we will see how to select multiple columns while joining both the table.

Product 1

Product 2

• In Power Query Editor, click on the Merge queries-> Merge queries as new.
• Then Merge queries windows will open, and select the table you want to merge.
• To select multiple columns, press ctrl and click on the columns, you want to choose in both the table.
• Then select the join from the options and click on ok.

• Click on the icon to expand the table-> select the Expand option and choose the column you want to display.

Now, you can see the two tables join on multiple columns using the Power query editor.

This is an example of how to use power query join on multiple columns in Power BI.

Read Power Bi relative date slicer

Power BI how to use power query union tables

Here we will see how to use power query to union tables in Power BI.

For this example, we have two tables one is the Product table and the Order table, we will union tables using the Append queries feature in Power Query.

Product table

Order table

• To union the two tables, in the Power Query editor, click on the Append queries-> select Append queries as new from the ribbon.

• Append queries window will open, select the Two tables and then select the First table and second table. Click on Ok.

Now you can see the union of tables in the Power Query editor.

This is an example of how to use power query union tables in Power BI.

Read Power bi shared dataset permissions management

Power BI how to use power query unpivot

Here we will see how to unpivot the table using the Power Query editor in Power BI.

Unpivoting is the process that will convert the column name into a single column into rows and the values are transformed into a single column.

For this example, we have a pivot table i.e. product table having a header or column name based on the product and value based on price.

To unpivot the above tables follow the below steps:

• In the Power Query editor, select the Transform tab-> then select all columns with ctrl +click on the column.

• Once you select all the columns, in the Transform tab click on the Unpivot Columns-> Unpivot Columns from the option.

Now you can see the table is unpivoted, the column header is converted into a single column, and the values also.

This is an example of how to use power query unpivot in Power BI.

Read Scheduled Power BI report data refresh

This is an example of how to use power query count rows in Power BI.

Power BI how to use power query remove rows based on the value

Here we will see how to remove rows based on the specific value using the Power Query editor in Power BI.

To remove the 1st and 2nd rows from the below table using the Power Query editor in Power BI.

• In the Power Query editor, select the column and go to the Home tab-> click on Remove rows.
• There are 6 different options, you can remove the rows from the table based on your need.
• In this case, we will select Remove Top rows-> Remove Top rows window will open. Provide the number of rows you want to remove from the top. Click on Ok.

• Now you can see the rows are removed from the table in the Query Editor.

This is an example of how to use power query to remove rows based on the value in Power BI

Read Power BI nan error

Power BI how to use power query replace values

Here we will see how to replace values using the Power Query editor in Power BI.

We will use the below table to change the age of Ron Smith from 23 to 25 by using the Replace values method.

• In Power Query Editor, go to the Home page, and select the value from the column.
• Then click on Replace values option from the ribbon.
• Next, the Replace value windows will open, so here you can see the value under the Value to Find option, and under Replace with option write the value you want to replace.

• Next, you can see the 23 value is replaced with 25 in the Age column.

This is an example of how to use the Power query to replace values in Power BI.

Read Power BI report using People Picker Field

Power BI how to use power query sort multiple columns

Here we will see how to sort data on multiple columns using the Power Query editor in Power BI.

With the help of the sort feature, you can arrange the data in ascending order(A-Z) or descending order(Z-A).

For this example, we will use the below table and sort the name column based on that it will sort the other column.

To sort the multiple columns in the Power Query editor, follow the steps below:

• In the Power Query editor, click on the column based on which you want to sort the other columns.
• Then in the Home tab, click on the Az icon in the Sort section, or else you can sort the column by clicking on the down arrow next to the column name-> Select Sort Ascending.

• In this case, I have sorted the name in the Ascending order, i.e. A to Z, based on that other columns are sorted accordingly.

This is an example of how to use a power query to sort multiple columns in Power BI.

Read Data Labels in Power BI

Power BI how to use power query sum column

Here we will see how to use the Power query to sum the column in Power BI.

We will create a new custom column and then calculate the sum of the price from the below table.

To get the sum of the Price column follow the below steps:

• In the Power Query editor, go to the add column tab, then click on the Custom column from the ribbon.
• Then the custom column window will open, provide the column name and then provide the below formula.

List.Sum(#"Changed Type"[Price])

Now you can see the output of the sum of the price column in the Power query.

This is an example of how to use Power query to sum column in Power BI.

Read Power bi table visualization

Power BI how to use power query count rows

Here we will see how to count rows using the Power Query editor in Power BI.

To see the count rows for each column in the Power query editor.

In the Power Query editor, go to the View tab-> then select the Column Profile option from the ribbon. Now you can see the count rows of each column.

This is how to use count rows using Power Query in Power BI.

Read Subtraction in Power bi using DAX

Power BI how to use power query replace null

Here we will see how to replace the null value with 0 using the Power query editor.

For this example, we will use the below table to replace the null value in the Price column with 0 using the Query Editor in Power BI.

To replace the null value with 0 in the Power Query editor, follow the below steps:

• In the Power Query editor, go to the Home tab-> select the Price column-> then click on the Replace values.
• Then Replace window will open, provide the value which will be replaced i.e. null, then provide the value you want to replace with i.e. 0.

• Now you can see the null value is replaced with 0 using the Power Query editor in Power BI.

This is an example of how to use a power query to replace the null value in Power BI.

Read DAX Filter function (Text column) in Power BI

Power BI how to use power query get the previous row

Here we will see how to get the previous row data using the Power Query editor in Power BI.

For this example, we have the below table and we will get the previous row value of the price column.

To get the value of the previous row in the Power Query editor, follow the below steps

• First, we will sort the column, to sort the Price column, click on the arrow then select the Sort ascending option.

• Then we will add the index column, and go to Add column tab-> Index column-> From 0 from the ribbon.

• To get the previous row we will add 1 to the index, so select the index column-> to go to Add column tab-> click on the Standard -> select Add.
• Then Add window will open, and provide 1 in the Value field. Click on Ok.

• Next, we will Merge the table with itself, so select the Merge queries from the Home tab.
• Then Merge queries window will open, and select the same table to merge.
• Then select the Index column( first table) and select the Addition column(Second table)
• Select join as Left Outer. Click on OK.

• Then click on the icon-> select the Expand option-> select the Price column-> click on Ok.

• Now you can see the previous value of the price column in the Power query editor.

This is an example of how to use the power query to get the previous row in Power BI.

Read Get Current Month Sales Report using Power BI Measure

Power BI how to use power query remove null rows

Here we will see how to remove null rows using the Power Query editor in Power BI.

For this example, we will use the below table and remove the null or blank rows from the table.

• In the Power Query editor, go to the Home tab-> select Remove rows-> Remove Blank Rows from the ribbon.

• Now you can see the blank or null rows are removed from the table.

This is an example of how to use a power query to remove null rows in Power BI.

You may like the following Power BI tutorials:

• Get Next Month Sales Report in Power BI
• Microsoft Power BI Alerts
• Export Power BI reports to PDF
• How to add a column with a dropdown list in Power Query
• Export Power BI Reports to Excel
• How to use weekday function power bi with example

Conclusion

In this Power BI tutorial, we learned how to create a table in the Power Query editor and also we discuss the below example:

• Power BI how to use power query to create a New table
• Power BI how to use blank query
• Power BI how to use power query add a column from another table
• Power BI how to use power query remove duplicates
• Power BI how to use power query add a column
• Power BI how to use power query add column if statement
• Power BI how to use power query based on condition
• Power BI how to use power query and operator
• Power BI how to use power query append tables
• Power BI how to use power query reference another table
• Power BI how to use power query by multiple columns
• Power BI how to use power query add row
• Power BI how to use power query combine columns
• Power BI how to use power query calendar table
• Power BI how to use power query lookup value from another table
• Power BI how to use power query hide column
• Power BI how to use power query join table
• Power BI how to use power query join on multiple columns
• Power BI how to use power query union tables
• Power BI how to use power query unpivot
• Power BI how to use power query remove rows based on the value
• Power BI how to use power query sort multiple columns
• Power BI how to use power query sum column
• Power BI how to use power query select column
• Power BI how to use power query count rows
• Power BI how to use power query not equal
• Power BI how to use power query not null
• Power BI how to use power query replace null
• Power BI how to use power query get the previous row
• Power BI how to use power query remove null rows