How to remove rows in power query editor [With various examples]

This Power BI Tutorial explains working with the CSV file in Power Query Editor. Here we will learn how to connect the CSV file to the Power Query and transform and load the data in Power BI. We will see, how to remove rows in power query editor with a few more examples.

Additionally, we will see how we can format a CSV file using a delimiter, and how to specify the CSV file path, all the columns, and their data types in the Power Query Editor.

Moreover, this tutorial will cover some more important functional kinds of stuff like:

• How to skip top rows from CSV file in Power Query
• How to remove Second Row in Power Query
• Use the power query editor to ensure the first row is used as a header
  • Change the Column Data type in Power Query Editor
• How to remove Column in Power Query
• Remove Errors in Power Query Editor
• Remove Bottom Rows Power Query
• Remove Column Header in Power BI
• Remove Duplicate rows in Power Query
  • Remove duplicates from multiple columns in Power Query
  • Remove duplicates from a single column in Power Query
• Remove Alternate rows in Power BI
• Remove Blank rows in Power Query
• Power query select columns dynamically
• Power Query Remove Rows with null in multiple columns
• Power query select columns by name
• Power query remove columns based on condition
• Remove other columns Power Query
• Hide columns in power query

For all the above things, we will see how to perform things manually and as well as dynamically in Power BI Power Query Editor.

Make a CSV File ready first

• As we started this post related to working with the CSV file in Power BI, so first we need to ready a CSV file with some attributes and records. The below screenshot represents my CSV file (named Financial Sheet) that has around 16 different columns and 29 rows or items.
• You can see the below CSV file is not in a proper format (having a delimiter as [“|“]) and also has some unwanted records (like some null or blank values).
• This CSV file is present in my local system with the .csv extension and we will use and convert this file into a proper format using the Power Query Editor.

Once the CSV file is ready, next we will see how we can connect this CSV file in Power BI.

Also, read: Power BI Report Export to PDF [With 20+ Examples]

Connect CSV File to Power Query Editor

• To work with the CSV file in Power BI, first, we need to connect the file to Power BI via the connector. To do so, Open Power BI Desktop -> Go to the Home tab -> Expand the Get data option -> Select Text/CSV as shown below.

• Next, select your specific CSV file (Financial Sheet) from your local system or any other drive, and then click on Open.

• Once you open it, you will get the below screen where you can see the File Origin, Delimiter, and the Data type Detection.
• Here, if you want to separate the columns using a custom delimiter (“|“) [because my CSV file has the | delimiter], then select the Delimiter as Custom and specify (“|“). Also, you have to choose the data type detection Based on an entire data set (if your file has more than 200 rows).
• But if you want to do it dynamically, then we can use the Query editor. That’s why without doing anything on this screen, directly hit on the Transform Data button.

• Now the query editor screen looks like the below screenshot. Here in the left navigation, it represents the CSV file name (Financial Sheet) whereas the right side of the screen represents the Properties pane including all the Applied steps (whatever you will change or modify in the query editor, it will be added step by step).

• Next, open the Advanced Editor under the Home tab. When you open it, you will look like the below-inbuilt code that defines your CSV file path (“C:\Users\Preeti\Desktop\Power BI\Financial Sheet.csv“) including the below things:

1. Delimiter = You can see a blank delimiter like ” ” or a delimiter with a comma (,) value.
2. Columns = It will show you the total number of columns that the specific CSV file contains. This option varies. It means, that sometimes it shows the proper value and sometimes it shows less than the total value.
3. Encoding = It specifies the default encoding value i.e. 1252.
4. QuoteStyle = The QuoteStyle will be None by default.
5. Changed Type = It transforms and creates the table columns (including their data types) from the CSV file.

let
    Source = Csv.Document(File.Contents("C:\Users\Preeti\Desktop\Power BI\Financial Sheet.csv"),[Delimiter="	", Columns=13, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}})
in
    #"Changed Type"

Refer to the below screenshot.

• To do it dynamically, we will just modify a little bit in the code. Change the Delimiter as “|” and Columns as 16. Because the CSV file has a total number of 16 columns.

let
    Source = Csv.Document(File.Contents("C:\Users\Preeti\Desktop\Power BI\Financial Sheet.csv"),[Delimiter="|", Columns=16, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}})
in
    #"Changed Type"

Once the modification is done, just click on the Done button.

• After that, the screen will display exactly like the below figure as we divided the columns using the delimiter.

Now we will see some functionality that how we can perform in the Power Query Editor in Power BI. Refer to the below instructions.

Also read: Clustered Column Chart in Power BI [With 45 Real Examples]

Remove Top Rows in Power Query

Suppose from the above CSV file, you want to remove or ignore some unwanted top rows from the Power Query Editor. In this case, you can do it manually and as well as dynamically as per your needs.

• To ignore the top rows manually in Power Query, go to the Home tab -> Click on Remove Rows -> Remove Top Rows.
• Next, it will ask you how many rows to remove from the top. Enter the number of rows (13) and click on the OK button. Then the specific top rows will remove from the query editor.

• The same thing if you want to do it dynamically, then click on the Advanced Editor and apply the below code (highlighted one) next to the Source code as shown below. Once you applied the code, do not forget to tap to the Done button.

let
    Source = Csv.Document(File.Contents("C:\Users\Preeti\Desktop\Power BI\Financial Sheet.csv"),[Delimiter="|", Columns=16, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",13)
in
    #"Removed Top Rows"

For this, we will use the Table.Skip function. As I wanted to skip the top 13 rows from the query editor, that’s why I specified the number as 13 in the Removed Top Rows step.

This is how we can skip the Top Rows in Power Query.

Check out: Power bi change color based on value [With 13 Real Examples]

Remove Second Row in Power Query

Here we will discuss how to remove any specific middle row from the Power Query editor. Suppose you want to remove the unwanted second row from the query editor, then in this case we will use the Table.RemoveRows function.

• As in this example, I am performing things one after another, that’s why I am adding a new step to the next one. Open the Advanced Editor and apply the code below (Highlighted one) next to the removed top rows step.

let
    Source = Csv.Document(File.Contents("C:\Users\Preeti\Desktop\Power BI\Financial Sheet.csv"),[Delimiter="|", Columns=16, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",13),
    #"Remove Rows"= Table.RemoveRows( #"Removed Top Rows",1)
in
    #"Remove Rows"

• The above code specifies the remove row value as 1 because this function (Table.RemoveRows) takes the row index number (and I want to remove the second row from the editor). Here, the row index starts from 0.
• Once you applied the code in the Advanced Editor, just click on the Done button. After a while, the second row has been removed from the query editor.

This is how to remove the Second Row in Power Query Editor.

Read: Power BI sync slicers [With 15 useful examples]

Make First Row as Header in Power BI

Now comes to how we can promote the first row as a header in Power Query. This thing we can do manually and as well as dynamically in Power BI.

• In the below screenshot, I want to make the first row (Segment, Country, Product, etc.) its header. To make it manually, go to the Home tab -> Expand Use First Row as Header (from top right corner) -> Select Use First Row as Headers. Then the first row will promote to the header in the query editor.

• To make the first row as a header in Power Query dynamically, we need to use the Table.PromoteHeaders (to promote header) and Table.TransformColumnTypes function (to specify all the columns including their data types).
• Apply the below code (highlighted one) in Advanced Editor where you have to put the field names as per the CSV file (all the column names should match with the CSV file column names).

let
    Source = Csv.Document(File.Contents("C:\Users\Preeti\Desktop\Power BI\Financial Sheet.csv"),[Delimiter="|", Columns=16, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",13),
    #"Remove Rows"= Table.RemoveRows( #"Removed Top Rows",1),
    #"Promoted Headers" = Table.PromoteHeaders(#"Remove Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"", type text}, {"Segment", type text}, {" Country", type text}, {" Product ", type text}, {" Discount Band ", type text}, {"Units Sold", Int64.Type}, {"Manufacturing Price", Int64.Type}, {"Sale Price", Int64.Type}, {"Gross Sales", Int64.Type}, {"Discounts", Int64.Type}, {" Sales", Int64.Type}, {"COGS#(tab)", Int64.Type}, {"Profit ", Int64.Type}, {" Date", type date}, {"Month Number", Int64.Type}, {"Month Name ", type text}})
in
    #"Changed Type1"

Where,

1. “”, “Segment“, ” Country“, ” Product “, ” Discount Band ” = Column names of the CSV file
2. text, Int64.Type, date = These are the various data types

Once you click on the Done button, then the first row will convert to the header in the Power Query editor.

This is how to make the first row a Header in Power Query Editor.

Change the Column Data type in Power Query Editor

• Sometimes what happens is, that the columns that retrieve from the CSV file are not having the same data type. That means you can view in the below screenshot, that the Gross Sales and Sales fields should have their data type as a number because it is an integer data type.
• But instead of the number data type, previously it was Int64.Type data type. If you want to modify any column data type, then you can edit it here directly inside the editor and click on Done.

This is how we can change the Column Data type in Power Query Editor.

Remove Column in Power Query Editor

Now we will see how to remove columns in Power Query Editor.

• Suppose there is an unnecessary column and you want to delete that from the Power Query. Not only a single column, but also you can delete multiple columns at a time in the Power Query Editor.
• To remove a single column from the query editor, select the column by clicking on it and then go to the Home tab -> Remove Columns -> Select Remove Columns.
• To remove multiple columns from the query editor, select all the columns (Ctrl+Click). Go to the Home tab -> Remove Columns -> Select Remove Columns as shown in the below screenshot.

• So the above steps represent how to remove the column manually in Power Query. Now we will see how we can delete the column dynamically in Power Query.
• To do it dynamically, we will use the Table.RemoveColumns function and need to specify the column name as shown below. Apply the highlighted code in the Advanced editor and tap on the Done button.

let
    Source = Csv.Document(File.Contents("C:\Users\Preeti\Desktop\Power BI\Financial Sheet.csv"),[Delimiter="|", Columns=16, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",13),
    #"Remove Rows"= Table.RemoveRows( #"Removed Top Rows",1),
    #"Promoted Headers" = Table.PromoteHeaders(#"Remove Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"", type text}, {"Segment", type text}, {" Country", type text}, {" Product ", type text}, {" Discount Band ", type text}, {"Units Sold", Int64.Type}, {"Manufacturing Price", Int64.Type}, {"Sale Price", Int64.Type}, {"Gross Sales", type number}, {"Discounts", Int64.Type}, {" Sales", type number}, {"COGS#(tab)", type number}, {"Profit ", Int64.Type}, {" Date", type date}, {"Month Number", Int64.Type}, {"Month Name ", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{""})
in
    #"Removed Columns"

Where,

{“”} = It’s the column name that I want to remove. To remove multiple columns, we can specify the column names with a delimiter comma (,) value.

After a while, the specific column(s) will be removed from the sheet.

This is how to remove columns in Power BI Power Query.

Remove Errors in Power Query Editor

Next comes how to deal with errors in the Power Query Editor.

In the Power Query Editor, sometimes you may face some errors while working on it. Suppose, you have a field called Gross Sales and you want to change its Data type to Decimal Number or Whole Number.

• When you are changing the data type, at the same time, you may face some errors with that specific field. To resolve it, go to the Home tab -> Expand Remove Rows -> Select Remove Errors. Then you can see the field error will disappear from the query editor.

• Similarly, to do it dynamically, we can use the Table.RemoveRowsWithErrors function where you need to specify the specific column name where that error occurs. Apply the code below (highlighted one) and click on the Done button.

let
    Source = Csv.Document(File.Contents("C:\Users\Preeti\Desktop\Power BI\Financial Sheet.csv"),[Delimiter="|", Columns=16, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",13),
    #"Remove Rows"= Table.RemoveRows( #"Removed Top Rows",1),
    #"Promoted Headers" = Table.PromoteHeaders(#"Remove Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"", type text}, {"Segment", type text}, {" Country", type text}, {" Product ", type text}, {" Discount Band ", type text}, {"Units Sold", Int64.Type}, {"Manufacturing Price", Int64.Type}, {"Sale Price", Int64.Type}, {"Gross Sales", type number}, {"Discounts", Int64.Type}, {" Sales", type number}, {"COGS#(tab)", type number}, {"Profit ", Int64.Type}, {" Date", type date}, {"Month Number", Int64.Type}, {"Month Name ", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{""}),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Removed Columns", {"Gross Sales"})
in
    #"Removed Errors"

Refer to the below screenshot.

This is how to remove Errors in Power Query Editor manually and as well as dynamically.

Read: Power BI Switch – DAX function

Remove Bottom Rows Power Query

In this topic, we will see how to remove bottom rows in Power Query.

Suppose you want to remove or ignore some unwanted bottom rows from the Power Query Editor. In this case, you can do it manually and as well as dynamically as per your needs.

• To ignore the bottom rows manually in Power Query, go to the Home tab -> Click on Remove Rows -> Remove Bottom Rows.
• Next, it will ask you how many rows to remove from the bottom. Enter the number of rows (4) and click on the OK button. Then the specific bottom rows will remove from the query editor.

• The same thing if you want to do dynamically, then click on the Advanced Editor and apply the below code (highlighted one) next to the Source code as shown below. Once you applied the code, do not forget to tap on the Done button.
• For this, we will use the Table.RemoveLastN function where you need to mention the number of bottom rows that you want to remove from the query editor.

let
    Source = Csv.Document(File.Contents("C:\Users\Preeti\Desktop\Power BI\Financial Sheet.csv"),[Delimiter="|", Columns=16, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",13),
    #"Remove Rows"= Table.RemoveRows( #"Removed Top Rows",1),
    #"Promoted Headers" = Table.PromoteHeaders(#"Remove Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"", type text}, {"Segment", type text}, {" Country", type text}, {" Product ", type text}, {" Discount Band ", type text}, {"Units Sold", Int64.Type}, {"Manufacturing Price", Int64.Type}, {"Sale Price", Int64.Type}, {"Gross Sales", type number}, {"Discounts", Int64.Type}, {" Sales", type number}, {"COGS#(tab)", type number}, {"Profit ", Int64.Type}, {" Date", type date}, {"Month Number", Int64.Type}, {"Month Name ", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{""}),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Removed Columns", {"Gross Sales"}),
    #"Removed Bottom Rows" = Table.RemoveLastN(#"Removed Errors",4)
in
    #"Removed Bottom Rows"

After a second, the number of bottom rows will disappear from the query editor. Refer to the below screenshot.

This is how to work with Remove Bottom Rows Power Query.

Remove Column Header in Power BI

Do you want to remove the column header in Power BI? In Power Query, we can remove the column header by demoting it to its first row. That means, simply we will convert the headers as the first row in the query editor.

• In the below screenshot, the first fig represents all the headers (Segment, Country, Product, etc.) present at the top of the editor. After we demoted the header to the first row, the editor looks like the second figure (the header comes to the first row).

• To make it, go to the Home tab -> Expand Use First Row as Headers -> Select Use Headers as First Row as like below. Then the headers have converted to the First row in the query editor.

• In case you want to demote the header dynamically, then you can use the Table.DemoteHeaders function whereas the Table.TransformColumnTypes function helps to convert the headers to columns (like Column1, Column2, etc.)
• To do so, open the Advanced Editor and apply the highlighted code below as shown below.

#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{""}),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Removed Columns", {"Gross Sales"}),
    #"Removed Bottom Rows" = Table.RemoveLastN(#"Removed Errors",4),
    #"Demoted Headers" = Table.DemoteHeaders(#"Removed Bottom Rows"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type text}})
in
    #"Changed Type2"   

• Once you have applied the code, tap on the Done button. After a second, you can see the headers have converted as the First row in the query editor.

This is how we can remove the column Header in Power BI.

Remove Duplicate rows in Power Query

Do you want to remove duplicate rows in Power Query Editor? Yes, you can do it manually and as well as dynamically in Power BI Power Query. Follow the instructions below.

• The below screenshot represents an Excel sheet named Sales_Table. This table contains six various fields (with different data types). Such as:

1. Product Name = Text Data type
2. Sales = Decimal Number Data type
3. Quantity = Whole Number Data type
4. Customer Name = Text Data type
5. Customer Location = Text Data type
6. Order Date = Date Data type

Also, this sales table has some records as shown below.

At first, you need to connect the Excel sheet to Power BI Desktop through the Excel workbook connector. Once you connect the sheet and transform the data, the spreadsheet will look like the screen below.

Now we will see how to remove or delete the duplicate rows or columns in Power Query. We can remove the duplicate values from multiple columns as well as a single column in Power Query Editor.

Remove duplicates from multiple columns in Power Query

Here we will see how to delete duplicate values from multiple columns in Power Query.

• In the below screen, you can see there are some duplicate rows like Smart Phone and Washing Machine. Also, all the field values are the same in the table.

• To make this table a distinct table, Select all the columns in the sales table (Ctrl+click) -> go to the Home tab -> Expand Remove Rows -> Select Remove Duplicates.

• Now you can see the duplicate rows have been deleted from the sales table and displayed with all the unique rows as shown below.

• Similarly to remove the duplicate column from multiple columns dynamically, we can use the Table.Distinct function. Open the Advanced Editor, and apply the code below (highlighted one) in the query editor.

let
    Source = Excel.Workbook(File.Contents("C:\Users\Preeti\Downloads\Sales.xlsx"), null, true),
    Sales_Table_Table = Source{[Item="Sales_Table",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sales_Table_Table,{{"Product Name", type text}, {"Sales", type number}, {"Quantity", Int64.Type}, {"Customer Name", type text}, {"Customer Location", type text}, {"Order Date", type date}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type")
in
    #"Removed Duplicates"

• Once you apply the code, click on the Done button. After a while, the duplicate rows have been deleted from the table.

This is how to remove duplicates from multiple columns in Power Query.

Remove duplicates from a single column in Power Query

Next, we will see how to remove or delete duplicate values from a single column in Power BI Power Query Editor.

• In the below screenshot, there is a field called Customer Name which has 4 duplicate values. Here, we wish to get rid of such duplicates and maintain just distinct values.
• To remove the duplicates from the Customer Name field, go to the Home tab -> Expand Remove Rows -> click on Remove Duplicates.

• Once it is done, you can see the table will display all the unique values in the Customer Name field.

• Similarly to remove the duplicate column from a single column dynamically, we can use the Table.Distinct function where you need to mention the specific field name i.e. Customer Name. Open the Advanced Editor, and apply the code below (highlighted one) in the query editor.

let
    Source = Excel.Workbook(File.Contents("C:\Users\Preeti\Downloads\Sales.xlsx"), null, true),
    Sales_Table_Table = Source{[Item="Sales_Table",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sales_Table_Table,{{"Product Name", type text}, {"Sales", type number}, {"Quantity", Int64.Type}, {"Customer Name", type text}, {"Customer Location", type text}, {"Order Date", type date}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type", {"Customer Name"})
in
    #"Removed Duplicates"

• Once you apply the code, click on the Done button. After a while, the duplicate column values have been deleted from the table.

This is how to remove duplicate rows in Power Query Editor.

Also read: Power bi table visualization

Remove Alternate rows in Power BI

In this section, we will see how to remove alternate rows in Power Query.

• When you wish to take the row out of the center in the Power query, you must use the Alternate rows option. Once you click on it, three fields will appear for you to fill out. Such as:

1. First row to remove = Indicate the row index number you want to start skipping from.
2. Number of rows to remove = Each time, enter how many rows you wish to eliminate.
3. Number of rows to keep = You can enter how many rows you wish to preserve.

Now see how it works in Power Query Editor.

• To remove any alternate row in Power Query Editor, go to the Home tab -> Expand Remove Rows -> Select Remove Alternate Rows.

• Next, a pattern box will appear where you have to mention the pattern of rows to remove and keep. As you can see I have mentioned the row numbers in every field i.e. 4, 2, and 5 as shown below. Click on OK.

• See in the below figure, I have a total of 12 rows i.e. from Laptop to null value (from Product Name field). When I am clicking on the OK button from the pattern box, then the 4th and 5th rows will be removed (because I entered 2 rows to be removed from the 4th position), and then more 5 records should be kept, and then again next 2 rows will also be removed.

• Then the final result table will appear like the below screenshot.

• All the above things I have done manually in the Power Query Editor. The same thing if you want to do dynamically, then we need to use the Table.AlternateRows function. In this function, we need to specify three parameter values: First row to remove (3), Number of rows to remove (2), and Number of rows to keep (5) as we manually do.
• Open the Advanced Editor and apply the highlighted code below in the Power Query editor.

let
    Source = Excel.Workbook(File.Contents("C:\Users\Preeti\Downloads\Sales.xlsx"), null, true),
    Sales_Table_Table = Source{[Item="Sales_Table",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sales_Table_Table,{{"Product Name", type text}, {"Sales", type number}, {"Quantity", Int64.Type}, {"Customer Name", type text}, {"Customer Location", type text}, {"Order Date", type date}}),
    #"Removed Alternate Rows" = Table.AlternateRows(#"Changed Type",3,2,5)
in
    #"Removed Alternate Rows"

• Once you have applied the code, click on the Done button. After a while, you will see the specified alternate rows have been deleted in the query editor.

This is how we can delete alternate rows in Power BI.

Check out: Power BI IF + 31 Examples

Remove Blank rows in Power Query

Do you want to delete the blank or null rows in Power Query Editor? Yes, you can remove it from the query editor manually and as well as dynamically.

• In the below screenshot, you can see the 12th row is an empty or null row. This unnecessary empty row I want to remove from the query editor.
• Manually, we can remove the blank rows by using the Remove Blank Rows (Home -> Remove Rows -> Remove Blank Rows) option in the query editor.

• After a while, the empty row will disappear in the query editor and the result will look like the figure below.

• Similarly, if you want to remove the null value in the Power query dynamically, then we can use the Table.SelectRows, List.IsEmpty, List.RemoveMatchingItems, and Record.FieldValues functions. Open the Advanced Editor and refer to the highlighted code below.

let
    Source = Excel.Workbook(File.Contents("C:\Users\Preeti\Downloads\Sales.xlsx"), null, true),
    Sales_Table_Table = Source{[Item="Sales_Table",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sales_Table_Table,{{"Product Name", type text}, {"Sales", type number}, {"Quantity", Int64.Type}, {"Customer Name", type text}, {"Customer Location", type text}, {"Order Date", type date}}),
    #"Removed Blank Rows" = Table.SelectRows(#"Changed Type", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
in
    #"Removed Blank Rows"

• Once you click on the Done button, then the null row will be removed from the Power Query editor.

This is how to remove Blank rows in Power Query.

Read: Power BI Measure Sum and Subtract Example

Power query select columns dynamically

Suppose in Power Query, you want to select some specific columns dynamically. For example, in the below screenshot, the table has a total of six columns where I want to select only two columns (Sales and Customer Name) dynamically.

• The below screen represents the proper result that displays only the specific selected two columns in the Power Query editor.

• To work around this, we will use the Table.SelectColumns function. Open the Advanced Editor (Home -> Advanced Editor) and put the below-highlighted code:

let
    Source = Excel.Workbook(File.Contents("C:\Users\Preeti\Downloads\Sales.xlsx"), null, true),
    Sales_Table_Table = Source{[Item="Sales_Table",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sales_Table_Table,{{"Product Name", type text}, {"Sales", type number}, {"Quantity", Int64.Type}, {"Customer Name", type text}, {"Customer Location", type text}, {"Order Date", type date}}),
    #"Selected Columns" = Table.SelectColumns(#"Changed Type",{"Sales", "Customer Name"})
in
    #"Selected Columns"

Where,

“Sales”, “Customer Name” = Specify the column names that you want to select in the query editor

Once you click on the Done button, you can see the result after a while.

This is how to work with Power query select columns dynamically.

Power Query Remove Rows with null in multiple columns

To work with the remove Rows with null in multiple columns in Power Query, check out this Power BI forum to get more: Power Query Remove Rows with null in multiple columns

Power query select columns by name

Would you like to work with power query select columns by name? Refer to the below scenario.

• In the screenshot below, you can see there is a Customer Location field in the query editor table. Now, I would like to display the records based on a specific location i.e. Alaska.
• That means the query table will filter and display only those records that are having the Alaska value. As there are only two records (Row 1 and 10) that belong to Alaska, that’s why there are two rows i.e. displayed in the result table.

• To achieve this, we will use the Table.SelecteRows and Text.StartsWith function. Open the Advanced Editor (Home -> Advanced Editor) and apply the highlighted code below in the query editor.

let
    Source = Excel.Workbook(File.Contents("C:\Users\Preeti\Downloads\Sales.xlsx"), null, true),
    Sales_Table_Table = Source{[Item="Sales_Table",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sales_Table_Table,{{"Product Name", type text}, {"Sales", type number}, {"Quantity", Int64.Type}, {"Customer Name", type text}, {"Customer Location", type text}, {"Order Date", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each Text.StartsWith([Customer Location], "Alaska"))
in
    #"Filtered Rows"

• Here, Customer Location specifies the field name where the filter value is present and Alaska represents the value that we want to filter in the Power Query editor.
• When you applied the code, click on the Done button and the result will appear in the query editor.

This is how we can select columns based on name in the Power query.

Also, read: Power BI Add Calculated Column [With Various Examples]

Remove other columns Power Query

There are two options that can be used to remove the columns in Power Query. Such as:

1. Remove Columns = To remove a single or multiple columns in the query editor, you can use the Remove columns option (Home -> Remove Columns -> Remove Columns). To select multiple columns in query editor, you can use Ctrl + Click or Shift + Click.
2. Remove Other Columns = This option we can use to remove all the columns except the selected columns in the query editor.

Now the thing is how to remove other columns in Power Query editor. To work around with this, we will use the Remove Other Columns option.

Select some specified columns that you want to keep in the query table and then go to Remove Columns -> Select Remove Other Columns as shown below.

Example:

• Here, I want to remove two unnecessary columns i.e. Quantity and Customer Location. I have selected the other columns (Product Name, Sales, Customer Name, and Order Date) except for these two columns (Quantity, Customer Location) in the query table.
• To remove those unwanted columns, expand the Remove Columns -> tap on the Remove Other Columns.

• The below screenshot represents the result screen after removing the other fields from the Power Query editor.

• Similarly, to do it dynamically, we need to use the Table.SelectColumns function. Open the Advanced Editor and apply the code below:

let
    Source = Excel.Workbook(File.Contents("C:\Users\Preeti\Downloads\Sales.xlsx"), null, true),
    Sales_Table_Table = Source{[Item="Sales_Table",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sales_Table_Table,{{"Product Name", type text}, {"Sales", type number}, {"Quantity", Int64.Type}, {"Customer Name", type text}, {"Customer Location", type text}, {"Order Date", type date}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Product Name", "Sales", "Customer Name", "Order Date"})
in
    #"Removed Other Columns"

This is the process to remove or delete other columns in Power Query Editor.

Hide columns in power query

Do you know what is the easiest way to hide the columns in Power Query? Refer to this MSDN guide to get more details about it: Power Query hide columns

Also, you may like some more Power BI tutorials:

• Power BI filter between two dates [With 15+ Examples]
• Power BI Sum Multiple columns
• Power BI divides two columns [With Real Examples]
• Stacked Bar Chart in Power BI [With 27 Real Examples]
• Power BI Bookmarks [With 21 Examples]
• Power Query Add Column

In this Power BI Tutorial, we discussed how to work with the CSV file in Power Query Editor. Also, we learned how to connect the CSV file to the Power Query and transform and load the data in Power BI.

Also, we saw how to format a CSV file using a delimiter, and how to specify the CSV file path, all the columns, and their data types in the Power Query Editor.

Moreover, this tutorial covered other functional kinds of stuff like:

• How to skip top rows from CSV file in Power Query
• How to remove Second Row in Power Query
• Use the power query editor to ensure the first row is used as a header
  • Change the Column Data type in Power Query Editor
• How to remove Column in Power Query
• Remove Errors in Power Query Editor
• Remove Bottom Rows Power Query
• Remove Column Header in Power BI
• Remove Duplicate rows in Power Query
  • Remove duplicates from multiple columns in Power Query
  • Remove duplicates from a single column in Power Query
• Remove Alternate rows in Power BI
• Remove Blank rows in Power Query
• Power query select columns dynamically
• Power Query Remove Rows with null in multiple columns
• Power query select columns by name
• Power query remove columns based on condition
• Remove other columns Power Query
• Hide columns in power query