Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Piotr12
Frequent Visitor

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

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.

 

 

1 ACCEPTED SOLUTION
v-yilong-msft
Community Support
Community Support

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.

vyilongmsft_0-1719379630024.png

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.

View solution in original post

2 REPLIES 2
v-yilong-msft
Community Support
Community Support

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.

vyilongmsft_0-1719379630024.png

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
Super User
Super User

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.










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors