Can you un-filter as a step in Power Query?

[vahju]

Hi I am a newb at Power Query and just learning the basics.

I am able to get my data merged between 3 tables and this adds 2 columns at the end of my Data table.
I want to replace some data in one column based on a filter in another column.
After I replace the data I want to un-filter the data so I am looking at the entire data set again but there appears to be no option to un-filter data once its filtered in power query.

For example:
Have 3 columns
Column 1: Unique value used to merge all 3 sheets
Column 2: Contains text values and will get some of the values replaced (data in rows: 8,9,10,11,null,Unknown)
Column 3: Contains text values that will be filtered to assist with replacing values in Column 2 (data in rows: confirmed, not confirmed, unknown, null)
Filter on column 3 for confirmed only
Replace values in column 2 (8,9,10,Unknown) with 11
The data table will need to contain all rows from the initial merge with the replaced values in Column 2.

Hopefully that makes sense.
Right now I am doing this with a bunch of steps using vlookup/countifs/and manual filtering.


Thanks ahead of time.

 

[kitjosh1050]

Sounds like you should create a Custom Field that will either take the value from Column 2 or replace it with something else depending on your filtering logic. Then you can replace Column 2 with the Custom Field.

For everything you need to know about the Power Query language for building that formula google the Power Query Language Specifications.

For a good introductory book to Power Query check out Chris Webb's book: Power Query for Power BI and Excel

 

[miguel.escobar]

Hey!

I believe that this might actually help you to certain extent:
https://www.youtube.com/watch?v=ikzeQgdKA0Q

If you can share a copy of your workbook with us, in order to work on it, that would be amazing.

 

[theBardd]

The filter only exists for the step in question, any subsequent steps are looking at the results of that filter, without a filter in operation, so there ius nothing to undo.

Instead of filtering, add your custom column, then just replace your nominated values.