Forum Discussion
If then statement, invalid identifier
Hi coreych,
You are not passing [Index] in this case, so when you are trying to use it PBI does not like it (cause it is not part of the context for the replacer funciton).
In your case, I think, it should be something like this (I am overloading the replacer function rather than the third parameter):
#"Replaced Value" = Table.ReplaceValue(#"Removed Columns", each [Index], null, (x,y,z)=> if y = "296" then "16942" else x, {"Player IDs.IDFANGRAPHS"})
Thanks jbwtp,
That got rid of the error, but it doesn't appear to have done anything in my query. Player 296 still has the wrong fangraphs ID. It also did a weird thing to the query language overall after I ran it.
The last statement in the query before I put your language in was...
in # "added index"After I ran your language it changed to...
in
#"Added Index"
in
#"Pitcher Contracts with IDs
Hi coreych,
This is a bit strange that the Index is text rather than number, but I guess this is Ok if PQ does not throw any error.
Do you want to use this code and check what it returns to Player IDs.IDFANGRAPHS and may be figre out how to set the condition to actually make the replacement (x should be the current Player IDs.IDFANGRAPHS value, y should be Index and z should be null)?
#"Replaced Value" = Table.ReplaceValue(#"Removed Columns", each [Index], null, (x,y,z)=> {x, y, z}, {"Player IDs.IDFANGRAPHS"})If it returns all variables as expected, do you wan tto try to make the Index to be of type number rather than text and change the filter accrodingly?
Cheers,
John
Hi John,
Thank you for your help with this. I think I'm not understanding something about what you wrote, that formula returns "token identifier expected" when I write it the way I THINK you intended, it doesn't do anything at all if I paste it directly in, but I think you intended me to change your x,y,z to some kind of actual value?.
I wrote it as...
#"Replaced Value" = Table.ReplaceValue(#"Removed Columns", each [Index], null, (16942,296,null)=> {16942, 296, null}, {"Player IDs.IDFANGRAPHS"})
16942 is the fangraphs ID I want player 296 associated with.
HI coreych,
The (x, y, z)=> in the template for the Replacer function. PBI automatically substitutes parameters to this funciton being: x - current value in the cell which is being checked, y - value which we compare to (this is the second parameter in the Table.ReplaceValue() function , in our case - the [Index] field), z - the value which we would like to replace it to (technicaly we pass it as null in the third parameter to Table.ReplaceValue() , but ignore in the body of the custom replacer function).
The output of the (x, y, z)=> funciton is assigned to the value in the target column (being Player IDs.IDFANGRAPHS).
If you set the step exactly as I wrote it, it should retun a list for each row in the Player IDs.IDFANGRAPHS field containing the {[value in the Player IDs.IDFANGRAPHS fireld], [value of the Index assosiated with this row], [null]}. If you alalyse the content of the lists for the rows that were failing you may find the problem.
Also, bear in mind that the error maybe in some other place as PBI uses "lazy" calcs, this step may be just a step where the problem surfaces itself, but not where it happens.
Cheers,
John
I'm also wondering how you concluded that the index is text rather than number. The index is not text, it IS a number.