top of page

You are learning Power Query in MS Excel

How to replace missing values with other values in Power Query?

To replace missing values in Power Query, follow these steps:

1. Load your data into Power Query:
- Open Excel and load your data into Power Query by selecting `Data` > `From Table/Range` (if your data is in a table) or `Get Data` > `From File` > `From Workbook` (for other Excel files).

2. Select the column with missing values:
- In the Power Query Editor, locate and select the column that contains missing values.

3. Replace missing values:
- Right-click on the column header and select `Replace Values`.
- In the dialog box that appears, leave the `Value to Find` field blank (to signify missing or null values).
- In the `Replace With` field, enter the value you want to use as a replacement.
- Click `OK`.

4. Alternative method using the Transform tab:
- With the column selected, go to the `Transform` tab on the ribbon.
- Click on `Replace Values`.
- In the dialog box, leave the `Value to Find` field blank.
- Enter the replacement value in the `Replace With` field.
- Click `OK`.

5. Alternative method using the Fill feature:
- If you want to fill missing values with the value from the cell above, you can use the `Fill` feature.
- Select the column with missing values.
- Go to the `Transform` tab on the ribbon.
- Click on `Fill` > `Down` or `Up` as needed.

6. Apply and load the changes:
- Once you have replaced the missing values, click `Close & Load` to apply the changes and load the data back into Excel.

By following these steps, you can effectively replace missing values in your data using Power Query.

bottom of page