locked
Change value based on value of another field RRS feed

  • Question

  • I have a field called Entity that is joined to another table in my Power Pivot data model. I want to change the value of the value from "113" to "163" if another field for the same record in the same table is a "HCWB" or "DSC". Can this be done on the import or do i need to add a custom column, make the change, copy over to the Entity field to maintain my data model and then delete custom field? If Elseif Then? 

    I can see myself making updates to a field based on the value of another. Along with hopefully the answer to my question above, could I get the general M syntax to change/update the field. I had looked at the Table.ReplaceValue() function but not sure I understand the parameters or the correct format of selecting field/column names.

    Thanks for any help,
    Eric
    Monday, February 20, 2017 5:55 PM

Answers

  • By substitution:

    TransformedColumn = Table.FromRecords( Table.TransformRows( <previousStepName>, (r) =>
    Record.TransformFields( r, { "Entity", each if r[Entity] = 113 and (r[Location] = "HCWB" or r[Location] = "DSC") then 163 else r[Entity] } ) ) )

    This is an advanced transformation, and if you are new to PQ, you may not fully comprehend the details. Nonetheless, it is the solution to your question. Since you are changing the value in one column based on a condition in another column, none of the standard functions like Table.ReplaceValue or Table.TransformColumns work in this case.

    • Marked as answer by dieffen Wednesday, February 22, 2017 3:57 PM
    Tuesday, February 21, 2017 1:01 PM

All replies

  • I don't understand the relationship between Entity field/other table and the values 113, "HCWB", and "DSC" (you don't say what table these values are coming from. However, the general pattern for changing the value in a column based on corresponding values in other columns is as follows (for illustration I'm using an Excel table with two columns - Value and Code:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
        TransformedColumn = Table.FromRecords(
                                Table.TransformRows(
                                    Source, 
                                    (r) => Record.TransformFields(
                                                r,
                                                {
                                                  "Value", 
                                                  each if r[Value] = 113 and (r[Code] = "HCWB" or r[Code] = "DSC") then 163 
                                                  else r[Value]
                                                }
                                           )
                                )
                             )
    in
       TransformedColumn

    Monday, February 20, 2017 9:01 PM
  • Sorry for not being too clear.  Was trying to be as concise as possible.  The source is a csv file.  The Entity field contains the 113, 153, 163, .... values.  Another field in the same file called Location has the "DSC" and "HCWB" and other values in it.  For only the records that have "DSC" or "HCWB" in the Location field, I want to change the Entity value from 113 to 163.

    Hopefully that clears up a little.

    thanks,

    Eric

    Monday, February 20, 2017 9:49 PM
  • Is there a common field to join the two tables?
    Monday, February 20, 2017 10:08 PM
  • Those two fields (Entity and Location) are in the same table.  The Entity field is joined to a table just to pick up the description of the code.  I thought I saw folks saying that you have to create a new field if you use an If ElseIf Then but I just wanted to maintain the join of the data model with existing Entity field.

    thanks,

    Eric

     
    Monday, February 20, 2017 10:46 PM
  • Hi Eric,

    Colin's code should work for you then.

    I cannot image of a way to shorten the code or make it easier, because the replacement-operation is based on a condition within the same record, so you basically need a row-context here.

    Another option would be to go "simple stupid":

    1) Add a new column where you perform the check & replacement row-wise

    2) Delete you old "Entity-column"

    3) Rename your new column from 1) to "Entity"

    I wouldn't expect any worse performance here. 


    Imke Feldmann TheBIccountant.com

    Tuesday, February 21, 2017 7:28 AM
  • Ok, thanks...I'm relatively new to PQ but I wasn't thinking that if I did the delete, rename method that the join in my data model on the deleted field would break also.  For clarification, I'm only checking the Location field to determine the value of the Entity field.  If [Location] ="DSC" or [Location]="HCWB" Then [Entity]="163" is the logic I'm going after.  I am assuming that if Location doesn't equal one of the two values that it remains as is.

    thanks,

    Eric

    Tuesday, February 21, 2017 11:20 AM
  • By substitution:

    TransformedColumn = Table.FromRecords( Table.TransformRows( <previousStepName>, (r) =>
    Record.TransformFields( r, { "Entity", each if r[Entity] = 113 and (r[Location] = "HCWB" or r[Location] = "DSC") then 163 else r[Entity] } ) ) )

    This is an advanced transformation, and if you are new to PQ, you may not fully comprehend the details. Nonetheless, it is the solution to your question. Since you are changing the value in one column based on a condition in another column, none of the standard functions like Table.ReplaceValue or Table.TransformColumns work in this case.

    • Marked as answer by dieffen Wednesday, February 22, 2017 3:57 PM
    Tuesday, February 21, 2017 1:01 PM
  • No, what the data model recognize is only the end-result of the query and not which steps you perform in order to get there. So if the substitute-column has exactly the same name and format, your connections in the model shouldn't be affected.

    Imke Feldmann TheBIccountant.com

    Tuesday, February 21, 2017 2:41 PM
  • Thank you.  I believe I understand the multiple functions.  I did get a "Token comma expected" error on the syntax...I saw where the were optional parameters in several of the functions where I wouldn't expect a comma necessary.

    thanks,

    Eric

    Tuesday, February 21, 2017 8:29 PM
  • Ok, good info to know. Thank you.
    Tuesday, February 21, 2017 8:30 PM
  • The set of functions above made the change from the 113 to the 163 in the Entity field.  That field is joined to another table in the data model to get the description.  After doing a refresh all in Power Pivot the description field still represents the entity code of 113 not the 163.  Do I need to have something special with the order of the queries?

    Also, I pull in a date that is text but I convert to Date data type early in the steps.  In the Preview pane I noticed that when I clicked on the step addressed in this thread which is the last step in the query that the data type icon for that date field changed from the calendar to alpha numeric.  I would have expected it to remain a date data type.

    I figured the Token Comma Expected issue out.  I didn't have a comma at the end of the prior step.

    Thanks,

    Eric

    Wednesday, February 22, 2017 4:12 PM
  • Sorry, I had replied to a response above and could be confusing with the chronological order. This is a copy of the post 2 above.

    The set of functions above made the change from the 113 to the 163 in the Entity field.  That field is joined to another table in the data model to get the description.  After doing a refresh all in Power Pivot the description field still represents the entity code of 113 not the 163.  Do I need to have something special with the order of the queries?

    Also, I pull in a date that is text but I convert to Date data type early in the steps.  In the Preview pane I noticed that when I clicked on the step addressed in this thread which is the last step in the query that the data type icon for that date field changed from the calendar to alpha numeric.  I would have expected it to remain a date data type.

    I figured the Token Comma Expected issue out.  I didn't have a comma at the end of the prior step.

    Thanks,

    Eric

    Wednesday, February 22, 2017 4:14 PM