Filling blank values in a dataset based on a shared value on a nother column

Hi Anonymous , Anonymous ,

We think maybe because it will filter entire table for every row, we make a optimize version as following,

1. Assume your last step called "#"Changed Type"", then add a additional step after it, 

BufferTable = Table.Buffer(Table.SelectRows(#"Changed Type",each [SFID]<>null)),

It will keep a small table in memory to speed our filter

2. create a custom column:

let c = [COMPANY],
t = Table.Buffer(
    Table.SelectRows(BufferTable,each [COMPANY]=c)
)
in
if [SFID] <> null 
then [SFID] 
else 
    if Table.RowCount(t)=0 
    then null 
    else Table.Max(t,{"SFID"})[SFID]

Then open advanced editor, change the 

#"Added Custom" = Table.AddColumn(BufferTable, "SFID_2", XXXXX)
to
#"Added Custom" = Table.AddColumn(#"Changed Type", "SFID_2", XXXXX)

3. remove column and rename the new column,

All the queries are here:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJ0cgaShkbGSrE60UpGcBEw1xiVawJkODm7AEkTUzOwiClcBMw1AzJc3dzB3FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [INVOICE = _t, COMPANY = _t, SFID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"INVOICE", Int64.Type}, {"COMPANY", type text}, {"SFID", Int64.Type}}),
    BufferTable = Table.Buffer(Table.SelectRows(#"Changed Type",each [SFID]<>null)),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "SFID_2", each let c = [COMPANY],
t = Table.Buffer(
    Table.SelectRows(BufferTable,each [COMPANY]=c)
)
in
if [SFID] <> null 
then [SFID] 
else 
    if Table.RowCount(t)=0 
    then null 
    else Table.Max(t,{"SFID"})[SFID]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"SFID"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"SFID_2", "SFID"}})
in
    #"Renamed Columns"

If you want a dax solution which will create an additional column and keep the old one, we will provide here.

Best regards,