Forum Discussion
If then statement, invalid identifier
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
Ok, I ran the code exactly as you typed it...
#"Replaced Value" = Table.ReplaceValue(#"Removed Columns", each [Index], null, (x,y,z)=> {x, y, z}, {"Player IDs.IDFANGRAPHS"})This code ran error free, but it does not appear to have done anything. I'm looking at my table exactly as it was, and player 296 still has the same fangraphs ID as player 297, that incorrect fangraphs ID is 27646 showing for both of them instead of 16942 for player 296 and 27646 for player 297.
I'm also wondering how you concluded that the index is text rather than number. The index is not text, it IS a number.
Hi coreych,
It is in "" in your code, numbers go without it.
This is the text type value: "26"
This is the number type value: 26
Cheers,
John
Ok, that makes some sense, but in no version of the query that returns an "invalid identifier" error does getting rid of the "" on 296 get rid of the "invalid identifier" error. In some cases taking the quotes off of "296" just moves the invalid identifier to the 16942, but that one IS text because many fangraphs IDs begin with "sa".