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

[Piotr12]

Hello,

I have a Power Bi that takes data from an excel - let's call it PEOPLE TABLE - that is generated regularly. Among many columns it contains people's names and their team's names. Certain people, because of some error, doesn't have their team's names generated and the field is blank. Let's call them lost people.

I want the people who generate the excel file and update the Power Bi dashboard not handle this issue each time. I'd like to have an excel file - let's call it LOST TABLE - where I can have two columns, one with those lost people's names and other with their proper team's names.

I want Power Bi to take those names from LOST TABLE, and check if the PEOPLE TABLE contain those names. If it does, then they need to repalce the Team Name column value, with the one that's in LOST TABLE Team Name column.

The idea is that those people might leave the company, other's might join that have the same issue, so someone can just write their names and proper team name into the LOST TABLE. Power Bi will then update their teams accordingly.

Thank's for any help.

[Anonymous · Accepted solution]

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.

[danextian]

Hi @Piotr12 

I would do a merge (or a join ni SQL) with the original table on the left and excel table on the right and joined on the people column. After that I'd create a new custom column that returns the excel table value if the orignal table value is blank.