Expression.Error: We cannot convert the value "Manual_inbound" to type Function.

[antalgu]

I'm having this error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

I know this is an error that appears when you present a field name in the code in a manner that he doesn't accepts, for example [ ] instead of " ". But I'm having 2 errors actually.

First I'm gonna explain what my code is supposed to do. I have a table (RegistroLlamadas) with phone calls each of which has a Campaign (Campaña) and some of them have an inbound number (DDI Entrante). Now I created a code to have a fixed value for DDI Entrante for 4 different Campaigns and to do this i use Table.TransformColumns. To do that I run this code in the power query editor in the advanced editor:

(The first 4 lines are not important but just to give context)

    Source = Access.Database(File.Contents("E:\Personal\Projectes\SOSMATIC\Post 3\ImportData.accdb"), [CreateNavigationProperties=true]),
    _RegistroLlamadas1 = Source{[Schema="",Item="RegistroLlamadas"]}[Data],
    _DuracioMenysDe15 = Table.SelectRows(_RegistroLlamadas1, each not ([Duracion de la llamada] <= 15 and [Campaña] = "Adamo_Ventas")),
    _CanviTipoTrucada = Table.ReplaceValue(Table.SelectRows(_DuracioMenysDe15, each ([Campaña] = "Manual_inbound" or [Campaña] = "Agenda_inbound" or [Campaña] = "Abandono_inbound" or [Campaña] = "Adamo_Ventas")),"Saliente manual", "LLamada entrante", Replacer.ReplaceText, {"Tipo de llamada"}),
    _Manualinbound = Table.TransformColumns(Table.SelectRows(_CanviTipoTrucada, each ([Campaña] = "Manual_inbound")),{[DDI_Entrante],"Manual_inbound"}),
    _Agendainbound = Table.TransformColumns(Table.SelectRows(_CanviTipoTrucada, each ([Campaña] = "Agenda_inbound")),{[DDI Entrante], "Agenda_inbound"}),
    _Abandonoinbound = Table.TransformColumns(Table.SelectRows(_CanviTipoTrucada, each ([Campaña] = "Abandono_inbound")),{[DDI Entrante], "Abandono_inbound"}),
    _AdamoVentas = Table.TransformColumns(Table.SelectRows(_CanviTipoTrucada, each ([Campaña] = "Adamo_Ventas")),{[DDI Entrante], "Adamo_Ventas"}),

Now, as I said in the beginning if I changed the way i refer to [Campaña] and [DDI Entrante] I should be able to solve the problem, If I change them to "Campaña" and "DDI Entrante" then I receive the error:

Expression.Error: We cannot convert the value "Manual_inbound" to type Function.
Details:
Value=Manual_inbound
Type=[Type]

Am I not understanding how transformcolumns works? Or is it maybe still about naming the fields?

If this explanation is not clear enough I can provide with an example dataset to illustrate the point

[Jimmy801]

Hello @antalgu 

the error is in your Table.TransformColumns. This function nees a nested list with your columnname as string and a function. Both is missing. So to fix this your code should look like this

Table.TransformColumns(Table.SelectRows(_CanviTipoTrucada, each ([Campaña] = "Manual_inbound")),{{"DDI_Entrante",each "Manual_inbound"}}),

And by the way i would choose another aproach to change the content of you column "DDD_Entrante".

Instead of Transforming you table 3 times by applying a SelectRows-function each time and probably combine them afterwards again, i would suggest you to us a Table.ReplaceValue. This would look like this (not complete, just to give you an idea)

Table.ReplaceValue(_CanviTipoTrucada,each [#"DDI_Entrante"],each if each [Campaña] = "Manual_inbound" then "Manual_inbound" else if ........, Replacer.ReplaceText, {"DDI_Entrante})

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[antalgu]

Hey @Jimmy801 

The first fix you provided worked like a charm, but just out of curiosity I tried out the second solution as it seemed more elegant, the problem I had was that if in the NewValue I used "each if each" at the beginning like you did it wouldn't work. I tried removing the 2nd each (like in the code below) and then only the else value worked, which means that only the Adamo_Ventas campaign had its "DDI Entrante" changed to "Adamo_Ventas". I don't really need a solution for this as the other code already works and this is just out of curiosity, but what is "each" word meaning in power bi? I can't seem to find anywhere an explanation about that keyword like other functions have and to me it seems the 2nd code you provided me with should work.

let
    Source = Access.Database(File.Contents("E:\Personal\Projectes\SOSMATIC\Post 3\ImportData.accdb"), [CreateNavigationProperties=true]),
    _RegistroLlamadas1 = Source{[Schema="",Item="RegistroLlamadas"]}[Data],
    _DuracioMenysDe15 = Table.SelectRows(_RegistroLlamadas1, each not ([Duracion de la llamada] <= 15 and [Campaña] = "Adamo_Ventas")),
    _CanviTipoTrucada = Table.ReplaceValue(Table.SelectRows(_DuracioMenysDe15, each ([Campaña] = "Manual_inbound" or [Campaña] = "Agenda_inbound" or [Campaña] = "Abandono_inbound" or [Campaña] = "Adamo_Ventas")),"Saliente manual", "LLamada entrante", Replacer.ReplaceText, {"Tipo de llamada"}),
    _CanviDDIEntrante = Table.ReplaceValue(_CanviTipoTrucada, each [#"DDI Entrante"], each if [Campaña] = "Manual_inbound" then "Manual_inbound" else if [Campaña] = "Agenda_inbound" then "Agenda_inbound" else if [Campaña] = "Abandono_inbound" then "Abandono_inbound" else "Adamo_Ventas", Replacer.ReplaceText, {"DDI Entrante"}),
    _RegistroLlamadas = Table.Combine({_CanviDDIEntrante, Table.SelectRows(_DuracioMenysDe15, each not ([Campaña] = "Manual_inbound" or [Campaña] = "Agenda_inbound" or [Campaña] = "Abandono_inbound" or [Campaña] = "Adamo_Ventas"))})
in
    _RegistroLlamadas