Forum Discussion

DIACHROMA's avatar
DIACHROMA
Helper II
5 years ago
Solved

Power Query - Replace values from another table without merging

Hi everyone,

 

I would like to replace values in a column from corresponding values in another table avoiding merging and expanding columns.

 

I have multiple queries with RecordType IDs in column. Instead of merging the RecordType table with all queries and expand the RecordType Name Column each time, I'd like to replace IDs with Names. 

 

I've tried the below : 

 

= Table.ReplaceValue(#"Changed Type", #"crm_RECORDTYPE"[ID] ,#"crm_RECORDTYPE"[NAME],Replacer.ReplaceText,{"RECORDTYPEID"})

 

But I got an error message : 

 

Expression.Error: We cannot convert a value of type List to type Text.
Details:
Value=[List]
Type=[Type]

 

Do you have an idea of what's wrong please ? 

 

Thank you very much, 

Pauline

  • = Table.ReplaceValue(#"Changed Type", each [RECORDTYPEID], each #"crm_RECORDTYPE"[NAME]{List.PositionOf(#"crm_RECORDTYPE"[ID], [RECORDTYPEID])}, Replacer.ReplaceValue, {"RECORDTYPEID"})

2 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    = Table.ReplaceValue(#"Changed Type", each [RECORDTYPEID], each #"crm_RECORDTYPE"[NAME]{List.PositionOf(#"crm_RECORDTYPE"[ID], [RECORDTYPEID])}, Replacer.ReplaceValue, {"RECORDTYPEID"})
  • Anonymous's avatar
    Anonymous
    Not applicable

    Try this formula:

     

    =let id = each [RECORDTYPEID] in Table.TransformColumns(#"Changed Type", {{"RECORDTYPEID", each Text.Replace(_, [RECORDTYPEID], Text.Find(crm_RECORDTYPE, id)[Name])}})

     

    --Nate