Forum Discussion
Helper ISelecting rows with condition based on other lines having common value
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
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
Super UserHi 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
vpsoiniHello 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
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