Conditional Fill Down

[Anonymous]

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!

[Anonymous · Accepted solution]

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.