Forum Discussion
Conditional Fill Down
I'm looking to perform a fill down based on another column. I'd like to fill down the "Property" column based on the "Invoice #" column. Whenever the invoice number column is duplicated, I'd like to fill down the property column.
End result: In the screenshot below, I'd like for "Brazilian" to fill down the next two columns (and no further).
Thank you so much!
Hi Anonymous ,
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WClIwNNI3NNAFkSZKOkpKsTo4BI0VUhIri2E8QwtjEyNLIM+pKLEqMyczMQ9VGAvPKzFP18gYyHPPSc1TcE1Mz0ktRkhA7IkFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Invoice#" = _t, Property = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Invoice#", type text}, {"Property", type text}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"Property"}), #"Grouped Rows" = Table.Group(#"Replaced Value", {"Invoice#"}, {{"Count", each List.Min([Property]), type nullable text}, {"Data", each _, type table [#"Invoice#"=nullable text, Property=nullable text]}}), #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Property"}, {"Data.Property"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Data",{"Data.Property"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Count", "Property"}}) in #"Renamed Columns"Then the result is as follows.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
Hi Anonymous ,
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WClIwNNI3NNAFkSZKOkpKsTo4BI0VUhIri2E8QwtjEyNLIM+pKLEqMyczMQ9VGAvPKzFP18gYyHPPSc1TcE1Mz0ktRkhA7IkFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Invoice#" = _t, Property = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Invoice#", type text}, {"Property", type text}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"Property"}), #"Grouped Rows" = Table.Group(#"Replaced Value", {"Invoice#"}, {{"Count", each List.Min([Property]), type nullable text}, {"Data", each _, type table [#"Invoice#"=nullable text, Property=nullable text]}}), #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Property"}, {"Data.Property"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Data",{"Data.Property"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Count", "Property"}}) in #"Renamed Columns"Then the result is as follows.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.