Sort a filtered table

[Anonymous]

Hi,

I have a table in Power Query that I would like to filter rows on a specific condition, containing specfic text, and then sort. Then I want to get the rows that I filtered out back. The problem is that I cant get them back, because the step in which I filtered the rows kind of removed the filtered out rows. 

Thankful for help!

[Jimmy801]

Hello @Anonymous 

if you need the filtered out rows, why then not only twist the criteria?

if you filter for all rows containting a number above 10 then twisting would be <=10.

If you need both tables at the same time (filtered and the lines filtered out) create a new step in the advanced editor and duplicate the step, rename the step and then twisting the criteria in the second step.

Hope this helps

Jimmy

[Anonymous]

Hi @Jimmy801 

Thanks for your reply! So my code in advance editor for the described steps are:

 #"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each ([Purchase order status] <> "Received" and [Purchase order status] <> "WRONG")),
    #"Sorted Rows1" = Table.Sort(#"Filtered Rows",{{"ETA Warehouse date", Order.Ascending}, {"Vendor", Order.Ascending}, {"Gender", Order.Ascending}, {"Category", Order.Ascending}, {"Article name", Order.Ascending}, {"Internal order type", Order.Ascending}})

So I have managed to sort all rows that do not have "Purchase order status" = Received or WRONG. Now I want to get all rows with Purchase order status = Received and WRONG to have the complete range again. I also want the Received and WRONG in the bottom of the table. How can I write the code for that in Advance editor. I have tried to reverse, but its acting lite Received and WRONG never existed...

[Jimmy801]

Hello @Anonymous 

I've prepared a solution for you. I've accessed your main table twice, once filtering for Rows without WRONG and Received and once with Wrong or Received. After that both table were sorted and at the end combined. Is this what you are looking for?

let
	Source = #table
	(
		{"Purchase order status","ETA Warehouse date","Vendor","Gender","Category","Article name","Internal order type"},
		{
			{"WRONG","43831","","","","",""},	{"Received","43831","","","","",""},	{"OK","43831","","","","",""},	{"Lost","43832","","","","",""},	{"WRONG","43832","","","","",""},	
			{"Received","43832","","","","",""},	{"Lost","43831","","","","",""},	{"Lost","43831","","","","",""},	{"Lost","43831","","","","",""},	{"OK","43832","","","","",""},	
			{"Received","43832","","","","",""},	{"WRONG","43832","","","","",""},	{"WRONG","43831","","","","",""},	{"Lost","43831","","","","",""},	{"OK","43831","","","","",""},	
			{"OK","43832","","","","",""},	{"OK","43832","","","","",""},	{"OK","43832","","","","",""}
		}
	),
    ToDate = Table.TransformColumns
    (
        Source,
        {
            {
                "ETA Warehouse date",
                each Date.From(Number.From(_)),
                type date
            }
        }
    ),
    FilterRowsNotWrongReceived = Table.SelectRows(ToDate, each ([Purchase order status] <> "Received" and [Purchase order status] <> "WRONG")),
    SortRowsNotWrongReceived = Table.Sort(FilterRowsNotWrongReceived,{{"ETA Warehouse date", Order.Ascending}, {"Vendor", Order.Ascending}, {"Gender", Order.Ascending}, {"Category", Order.Ascending}, {"Article name", Order.Ascending}, {"Internal order type", Order.Ascending}}),
    FilterRowsWrongReceived = Table.SelectRows(ToDate, each ([Purchase order status] = "Received" or [Purchase order status] = "WRONG")),
    SortRowsWrongReceived = Table.Sort(FilterRowsWrongReceived,{{"ETA Warehouse date", Order.Ascending}, {"Vendor", Order.Ascending}, {"Gender", Order.Ascending}, {"Category", Order.Ascending}, {"Article name", Order.Ascending}, {"Internal order type", Order.Ascending}}),
    CombineBothTable = Table.Combine({SortRowsNotWrongReceived,SortRowsWrongReceived})
in
	CombineBothTable

Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

Hi @Jimmy801 

Thanks a lot! I´m quite new to this so I´m not really getting where and what of it to insert to my existing code. The applicaple part of the code currently looks like this: 

let
    Source = Table.Combine({#"OFU SS20", #"OFU CN SS20", #"OFU REP/NOS/SP"}),

.

.

.

 
  #"Sorted Rows" = Table.Sort(#"Renamed Columns1",{{"Ex factory original", Order.Ascending}, {"Ex factory", Order.Ascending}, {"Purchase order status", Order.Ascending}, {"Not shipped quantity", Order.Ascending}, {"Announced quantity", Order.Ascending}, {"Received quantity", Order.Ascending}}),
 
  #"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each ([Purchase order status] <> "Received" and [Purchase order status] <> "WRONG")),
  
 #"Sorted Rows1" = Table.Sort(#"Filtered Rows",{{"ETA Warehouse date", Order.Ascending}, {"Vendor", Order.Ascending}, {"Gender", Order.Ascending}, {"Category", Order.Ascending}, {"Article name", Order.Ascending}, {"Internal order type", Order.Ascending}}),
 

->And now I want to get filtered row back - ie rows with Purchase order status "Received" and "Wrong"

[Jimmy801 · Accepted solution]

Hello @Anonymous 

copy paste my code into a new query and then replace the first 2 steps with your code until the step sorted rows.. meaning including this

  #"Sorted Rows" = Table.Sort(#"Renamed Columns1",{{"Ex factory original", Order.Ascending}, {"Ex factory", Order.Ascending}, {"Purchase order status", Order.Ascending}, {"Not shipped quantity", Order.Ascending}, {"Announced quantity", Order.Ascending}, {"Received quantity", Order.Ascending}}),

Then replace the "ToDate" of this step

FilterRowsNotWrongReceived = Table.SelectRows(ToDate, each ([Purchase order status] <> "Received" and [Purchase order status] <> "WRONG")),

with #"Sorted Rows"

BR

Jimmy

[Anonymous]

 Thanks, that worked perfect! @Jimmy801 

[Mariusz]

Hi @Anonymous 

Can you share a sample and expected outcome?

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.