If then statement, invalid identifier

[coreych]

Hi all, I'm working with a database of baseball players.  I need to match the contracts in my fantasy baseball league with the season projections I want for each player.  The season projections database has a unique identifier, but the contracts do not.  I've matched virtually every player using their name, but my method created a few duplicates.  I just need to force feed the correct player ID in the season projections into a couple of players who did not match correctly using their name.  I created the following query to replace the matching player's ID with the correct one.

    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns", each [PlayerIDs.IDFANGRAPHS], each if [Index] = “296” then “16942” else [PlayerIDs.IDFANGRAPHS], Replacer.ReplaceText, {"Player IDs.IDFANGRAPHS"}),

Index refers to the index column I created to identify the players with the same name as separate players, the player I want to change is 296, I need to change that player's fangraphs ID to 16942.  This returns a "Invalid Identifier" error on the 296 parameter.  What is the invalid identifier error exactly and how can I get it to go away and run my player ID replacement?

[coreych]

All I want to do is replace one player's fangraphs ID (currently 27646) with 16942.  There are two players with the same name who are listed as having fangraphs ID 27646, I need one of those two to change to 16942.

[wdx223_Daniel]

 #"Replaced Value" = Table.ReplaceValue(#"Removed Columns", each [#“PlayerIDs.IDFANGRAPHS”], each if [Index] = “296” then “16942” else [#“PlayerIDs.IDFANGRAPHS”], Replacer.ReplaceText, {"Player IDs.IDFANGRAPHS"})

[coreych]

Thanks for the help.  That code returned the same error, but it is now on the first "Player IDs.IDFANGRAPHS" parameter.

[jbwtp]

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"})

[coreych]

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

[jbwtp]

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

[coreych]

I'm also wondering how you concluded that the index is text rather than number.  The index is not text, it IS a number.

[jbwtp]

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

[coreych]

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".

[jbwtp]

Hi @coreych,

This is Ok, the field can be text, it should not be a problem.

Did you try this version of the replacer? What it returns on the "296" lines?

You can try using this knowledge for tweaking the actual replacer code.

Cheers,

John 

[coreych]

I don't really understand what the x,y,z is doing.  I think you wanted me to replace that with the values.  I wrote it this way and it returned the "invalid identifier" error on the first "16942" parameter.

...

#"Replaced Value" = Table.ReplaceValue(#"Removed Columns", each [Index], null, (“16942”,296,null)=> {“16942”, 296, null}, {"Player IDs.IDFANGRAPHS"})

[coreych]

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. 

[jbwtp]

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

[coreych]

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.

[jbwtp]

Hi @coreych,

the idea was that you will be able to check the parameters that are passed to the replacer funciton. If you can see the "ordinary" values in the Player IDs.IDFANGRAPHS column after executing this step, something clearly went wrong as it supposed to contant lists (containing the parameters passed to the replacer function), not text or numbers.

From this parameters you were expected to be able to see what exactly the filter needs to be.

Cheers,

John