Detecting rows and replacing their values in certain columns based on an input from excel file

Hi Piotr12 ,

First, you can create a LOST TABLE with two columns: one for the lost person's name and one for their correct team name. Use the left outer join merge table with the person's name in Power Query.

Replace the blank team name in the PEOPLE TABLE with the corresponding team name in the LOST TABLE. Refresh the data in Power BI to reflect updates when the LOST TABLE is modified. The following M code can be used for reference.

let
    People_Table = Excel.Workbook(File.Contents("path_to_PEOPLE_TABLE.xlsx"), null, true),
    Lost_Table = Excel.Workbook(File.Contents("path_to_LOST_TABLE.xlsx"), null, true),
    Merged_Table = Table.NestedJoin(People_Table,{"Name"},Lost_Table,{"Name"},"NewColumn",JoinKind.LeftOuter),
    Expand_TeamName = Table.ExpandTableColumn(Merged_Table, "NewColumn", {"Team Name"}),
    Replace_Blanks = Table.ReplaceValue(Expand_TeamName,"",each [NewColumn.Team Name],Replacer.ReplaceValue,{"Team Name"})
in
    Replace_Blanks

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.