Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Replacing generic values based on specific values in another column

I'm working on ingesting timesheet data. The data needs a fair amount of cleaning, all of which I have been able to do via queries except changing labor categories for certain employees.

 

Labor categories in the PLC column are not unique, we may have many DEV or DES resources. Employee IDs are unique. Because of the way this is put together, I want to keep the daily entries (meaning there is one row per daily entry of hours). I was hoping to be able to replace values in the PLC column based on the value in the Employee Id column. I don't want to replace every instance of a value in the PLC column, just specific instances where the employee was miscategorized.

 

As example, Employee ID "M1234" is Categorized as DEV in the [PLC] column, but should be SR DEV. I need to change DEV to SR DEV in all the rows that also contain "M1234" in the [Employee Id] column but not any of the other rows. 

 

Because life can never be easy, I need to do this for a number of miscategorized employees. I was hoping to do it via query (assuming a step per employee as the PLCs are different), so that I can save a template and just make a new report with new monthly data.


If anyone can help, please, please.

Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Anonymous ,

     

    We can use Table.ReplaceValue function in Power Query Editor to meet your requirement:

     

     

    Table.ReplaceValue(#"Changed Type",each [PLC],each if[Employee ID] = "M1234" then "SR DEV" else [PLC],Replacer.ReplaceValue,{"PLC"})

     

     

     

    All the queries are here:

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8jU0MjZR0lFycQ1TitWJVjJEYhsB2cFBCjCuMRI3FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Employee ID" = _t, PLC = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee ID", type text}, {"PLC", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",each [PLC],each if[Employee ID] = "M1234" then "SR DEV" else [PLC],Replacer.ReplaceValue,{"PLC"})
in
    #"Replaced Value"

     

     

    Please also refer to this similar thread:https://community.powerbi.com/t5/Power-Query/Table-Replace-Value-multiple-condition/td-p/785093


    If it doesn't meet your requirement, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.


    Best regards,

     

2 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    We can use Table.ReplaceValue function in Power Query Editor to meet your requirement:

     

     

    Table.ReplaceValue(#"Changed Type",each [PLC],each if[Employee ID] = "M1234" then "SR DEV" else [PLC],Replacer.ReplaceValue,{"PLC"})

     

     

     

    All the queries are here:

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8jU0MjZR0lFycQ1TitWJVjJEYhsB2cFBCjCuMRI3FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Employee ID" = _t, PLC = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee ID", type text}, {"PLC", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",each [PLC],each if[Employee ID] = "M1234" then "SR DEV" else [PLC],Replacer.ReplaceValue,{"PLC"})
in
    #"Replaced Value"

     

     

    Please also refer to this similar thread:https://community.powerbi.com/t5/Power-Query/Table-Replace-Value-multiple-condition/td-p/785093


    If it doesn't meet your requirement, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.


    Best regards,

     

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?


    Best regards,