Replace value based on conditions from multiple columns

[TCatron18]

I'm running into an issue trying to replace a value within a column that looks at a value within the column itself as well as a value within a separate column. 

I have column named [Sender's Position Category] that contains some null values. I am wanting to replace these null values with "xSystem Generated" if the [Sender's Position Category] = null and [Sender's Sr Business Unit] = "xSystem Generated", and all remaining null values would be "N/A".

I currently have this code, but it is not working as expected, it is changing all null values to "N/A":

= Table.ReplaceValue(#"Renamed Columns4",each null,each if "Sender's Sr Business Unit" = "xSystem Generated" and "Sender's Position Category" = null then "xSystem Generated" else "N/A",Replacer.ReplaceValue,{"Sender's Position Category"})

This code is giving me an invalid identifier error: 

= Table.ReplaceValue(#"Renamed Columns4",each [Sender's Position Category],each if [Sender's Sr Business Unit] = "xSystem Generated" and [Sender's Position Category] = null then "xSystem Generated" else "N/A",Replacer.ReplaceText,{"Sender's Position Category"})

Any help you can provide is appreciated.

[TCatron18]

I am still looking for a solution on this. Anyone have any advise on what I can do here? 

[vanessafvg]

can you share the actual error you are getting?  are you able to provide the whole script?

[TCatron18]

@vanessafvg It appears that it doesn't like the columns being in square brackets and wants them to be in quotations instead.

This is the whole script minus the step that I'm having issues with:

let
Source = Excel.Workbook(File.Contents("S:\HRIS\Reporting\Analytics\Honoring YOU\Access DB\All_Awards.xlsx"), null, true),
data0_Sheet = Source{[Item="data0",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(data0_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"AWARD ID", Int64.Type}, {"Date Submitted", type datetime}, {"Employee ID", Int64.Type}, {"RECIPIENT'S NAME", type text}, {"Recipient's Email", type text}, {"Award Type", type text}, {"Amount", Int64.Type}, {"Core Value", type text}, {"Reward Code", type any}, {"Patient Recognition", type text}, {"Award Status", type text}, {"Approve Date", type date}, {"RECIPIENT'S DIRECT MANAGER ID", Int64.Type}, {"RECIPIENT'S DEPARTMENT", Int64.Type}, {"RECIPIENT'S DEPARTMENT TITLE", type text}, {"Recipient's Affiliate", type text}, {"RECIPIENT'S HIRE DATE", type date}, {"Issuer Employee ID", type text}, {"Issuer's Name", type text}, {"Issuer's Email", type text}, {"ISSUER'S DIRECT MANAGER ID", Int64.Type}, {"Issuer's Manager Name", type text}, {"ISSUER'S DEPARTMENT TITLE", type text}, {"Sender's Affiliate", type text}, {"Delivery Method", type text}, {"Resolution Date", type datetime}, {"Sender Department ID", Int64.Type}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Recipient's Affiliate"}, #"Senoir Affiliate Table", {"Affiliate"}, "Senoir Affiliate Table", JoinKind.LeftOuter),
#"Expanded Senoir Affiliate Table" = Table.ExpandTableColumn(#"Merged Queries", "Senoir Affiliate Table", {"Sr Business Unit"}, {"Senoir Affiliate Table.Sr Business Unit"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Senoir Affiliate Table",{{"Senoir Affiliate Table.Sr Business Unit", "Recipient's Sr Business Unit"}}),
#"Merged Queries1" = Table.NestedJoin(#"Renamed Columns", {"Sender's Affiliate"}, #"Senoir Affiliate Table", {"Affiliate"}, "Senoir Affiliate Table", JoinKind.LeftOuter),
#"Expanded Senoir Affiliate Table1" = Table.ExpandTableColumn(#"Merged Queries1", "Senoir Affiliate Table", {"Sr Business Unit"}, {"Senoir Affiliate Table.Sr Business Unit"}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded Senoir Affiliate Table1",{{"Senoir Affiliate Table.Sr Business Unit", "Sender's Sr Business Unit"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns1",null,"xSystem Generated",Replacer.ReplaceValue,{"Sender's Affiliate"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,"xSystem Generated",Replacer.ReplaceValue,{"Sender's Sr Business Unit"}),
#"Merged Queries2" = Table.NestedJoin(#"Replaced Value1", {"Issuer Employee ID"}, #"Manager Data", {"Employee"}, "Manager Data", JoinKind.LeftOuter),
#"Expanded Manager Data" = Table.ExpandTableColumn(#"Merged Queries2", "Manager Data", {"AssignmentIsSupervisor"}, {"Manager Data.AssignmentIsSupervisor"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Manager Data",{{"Manager Data.AssignmentIsSupervisor", type text}}),
#"Replaced Value2" = Table.ReplaceValue(#"Changed Type1",null,"Employee",Replacer.ReplaceValue,{"Manager Data.AssignmentIsSupervisor"}),
#"Renamed Columns2" = Table.RenameColumns(#"Replaced Value2",{{"Manager Data.AssignmentIsSupervisor", "Is a Manager"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns2",{{"Employee ID", type text}}),
#"Merged Queries3" = Table.NestedJoin(#"Changed Type2", {"Employee ID"}, #"Category Data", {"EMPLOYEE"}, "Category Data", JoinKind.LeftOuter),
#"Expanded Category Data" = Table.ExpandTableColumn(#"Merged Queries3", "Category Data", {"POSITIONCATEGORY", "JOBCATEGORY"}, {"Category Data.POSITIONCATEGORY", "Category Data.JOBCATEGORY"}),
#"Renamed Columns3" = Table.RenameColumns(#"Expanded Category Data",{{"Category Data.POSITIONCATEGORY", "Recipient's Position Category"}, {"Category Data.JOBCATEGORY", "Recipient's Job Category"}}),
#"Merged Queries4" = Table.NestedJoin(#"Renamed Columns3", {"Issuer Employee ID"}, #"Category Data", {"EMPLOYEE"}, "Category Data", JoinKind.LeftOuter),
#"Expanded Category Data1" = Table.ExpandTableColumn(#"Merged Queries4", "Category Data", {"POSITIONCATEGORY", "JOBCATEGORY"}, {"Category Data.POSITIONCATEGORY", "Category Data.JOBCATEGORY"}),
#"Renamed Columns4" = Table.RenameColumns(#"Expanded Category Data1",{{"Category Data.POSITIONCATEGORY", "Sender's Position Category"}, {"Category Data.JOBCATEGORY", "Sender's Job Category"}})
in
#"Renamed Columns4"

[Bibiano_Geraldo]

Hi @TCatron18,

Apply this code in your query editor. This approach ensures that only null values are replaced based on the specified conditions.

= Table.TransformColumns(
    #"Renamed Columns4",
    {
        {
            "Sender's Position Category",
            each if [Sender's Sr Business Unit] = "xSystem Generated" and _ = null then "xSystem Generated"
                 else if _ = null then "N/A"
                 else _
        }
    }
)

I hope this helps! 🎯

If you found this answer helpful:

✔️ Mark it as the solution to help others find it faster.

👍 Give it a kudo to show your appreciation!

[TCatron18]

@Bibiano_Geraldo This still seems to be changing all of the null values to N/A. I'm also getting the 'invalid identifier' error when using the square brackets, but even when changing to quotations it's still changing all to N/A.