Selecting rows with condition based on other lines having common value

[vpsoini]

Hi.

I have table with sales lines, including items, item categories, locations and associated freight costs. Below is simplified example

Ordernumber	Item	Item category	Location	Quantity	Sum
PO001	Item1	101	Warehouse 1	1	10
PO001	Item2	110	Warehouse 1	2	12
PO001	Postage	900		1	5
PO002	Item3	200	Warehouse 2	2	25
PO002	Freight	900		1	9
PO003	Freight	900		1	10
PO003	Item 10	101	Warehouse 1	1	30
PO004	Item 5	105	Warehouse 2	1	16
PO004	Mail	900		1	4

My goal would be getting warehouse 1 lines filtered so, that it would include shipping costs (category 900) in those cases, where they belong to shipments from warehouse 1.  Unfortunately my data does not include Location info for those shipping costs lines, so the condition should be:

"include all lines where location code is "Warehouse 1" OR is empty AND ordernumber is same with lines, where location code = Warehouse 1"

The shipping cost line may have different "item names" (postage, freight etc.) but it has the same category (900) and is the only one with empty location info. The shiping cost line may be in any order (first , last, in the middle) compared to other lines in same order (with same PO-number), so it's not always e.g. last. 

My previous step in transforming data is "Removed columns" and the beginnig of the query could be:

= Table.SelectRows(#"Removed columns", each ([Location_Code] = "Warehouse 1" or [Location_Code] = "" and *insert magic here* ))

Could you help me figuring out how to gain this result after filtering:

Ordernumber	Item	Item category	Location	Quantity	Sum
PO001	Item1	101	Warehouse 1	1	10
PO001	Item2	110	Warehouse 1	2	12
PO001	Postage	900		1	5
PO003	Freight	900		1	10
PO003	Item 10	101	Warehouse 1	1	30

A big thanks in advance for your help 😊

[BA_Pete · Accepted solution]

Hi @vpsoini ,

You can fill down the warehouses in the data, the trick is making sure they're in an appropriate order:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvA3MDBU0lHyLEnNBdGGYF54YlFqRn5pcaoCWAwsrhSrg6rcCCQMFEdXDhY3QlEekF9ckpieCmRZGoA0KEBNNYWrMoIaagwywADVUCOooUaoyt2KUjPTM0owDLWEqzLGowrJQ8ZQuxXAnsEVAsYIDSYwDaZg9aYYrgXrMUNR75uYmYPhCBOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Ordernumber = _t, Item = _t, #"Item category" = _t, Location = _t, Quantity = _t, Sum = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source," ",null,Replacer.ReplaceValue,{"Location"}),
    #"Sorted Rows" = Table.Sort(#"Replaced Value",{{"Ordernumber", Order.Ascending}, {"Item category", Order.Ascending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows",{"Location"})
in
    #"Filled Down"

In this case I've sorted by [OrderNumber] then [Location], then used Transform tab > Fill Down on the [Location] column.

Fom here, your filters/calculations etc. should be easy.

Pete

[BA_Pete · Accepted solution]

Hi @vpsoini ,

You can fill down the warehouses in the data, the trick is making sure they're in an appropriate order:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvA3MDBU0lHyLEnNBdGGYF54YlFqRn5pcaoCWAwsrhSrg6rcCCQMFEdXDhY3QlEekF9ckpieCmRZGoA0KEBNNYWrMoIaagwywADVUCOooUaoyt2KUjPTM0owDLWEqzLGowrJQ8ZQuxXAnsEVAsYIDSYwDaZg9aYYrgXrMUNR75uYmYPhCBOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Ordernumber = _t, Item = _t, #"Item category" = _t, Location = _t, Quantity = _t, Sum = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source," ",null,Replacer.ReplaceValue,{"Location"}),
    #"Sorted Rows" = Table.Sort(#"Replaced Value",{{"Ordernumber", Order.Ascending}, {"Item category", Order.Ascending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows",{"Location"})
in
    #"Filled Down"

In this case I've sorted by [OrderNumber] then [Location], then used Transform tab > Fill Down on the [Location] column.

Fom here, your filters/calculations etc. should be easy.

Pete

[vpsoini]

Hello @BA_Pete 

There was a twist on this case, which is described in the picture below.  I can see, that the original solution was based on replacing " " with "null", but now I would need to be able to fill down only those cells with common order number with upper cell?

In picture below empty values in column "Location_Code" need to filled down, if there's line with same orderno having the location code.  So order numbers 300085,..88 and..93 should be left empty, since rows having both that number AND location code "VARAOSAV" do not exist and only rows below ordernumbers 300091 and ...94  should be filled, because they do have line(s) having both the same ordernumber AND location code present.

How that could be accomplished?

[BA_Pete · Accepted solution]

Hi @vpsoini ,

Try this code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvA3MDBU0lHyLEnNBdGGYF54YlFqRn5pcaoCWAwsrhSrg6rcCCQMFEdXDhY3QlEekF9ckpieCmRZGoA0KEBNNYWrMoIaagwyAKwGapIRqhq3otTM9IwSDJMs4aqM8ahC8oUx1EIFsA9wedsYocEEpsEUrN4URb0RzAIzFPW+iZk5GI4wgSsxRSiBGAj1s4VSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Ordernumber = _t, Item = _t, #"Item category" = _t, Location = _t, Quantity = _t, Sum = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source," ",null,Replacer.ReplaceValue,{"Location"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","",null,Replacer.ReplaceValue,{"Location"}),
    #"Sorted Rows" = Table.Sort(#"Replaced Value1",{{"Ordernumber", Order.Ascending}, {"Item category", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Ordernumber"}, {{"data", each _, type table [Ordernumber=nullable text, Item=nullable text, Item category=nullable text, Location=nullable text, Quantity=nullable text, Sum=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "fillLocation", each Table.FillDown([data], {"Location"})),
    #"Expanded fillLocation" = Table.ExpandTableColumn(#"Added Custom", "fillLocation", {"Item", "Item category", "Location", "Quantity", "Sum"}, {"Item", "Item category", "Location", "Quantity", "Sum"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded fillLocation",{"data"})
in
    #"Removed Columns"

Essentially the same as before but I've grouped on [Ordernumber] and nested All Rows, then performed the fill-down on the nested table, then expanded the new column.

I get the following output:

Pete