Forum Discussion
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
- CNENFRNLCommunity Champion
= Table.ReplaceValue(#"Changed Type", each [RECORDTYPEID], each #"crm_RECORDTYPE"[NAME]{List.PositionOf(#"crm_RECORDTYPE"[ID], [RECORDTYPEID])}, Replacer.ReplaceValue, {"RECORDTYPEID"}) - AnonymousNot 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