Power Query Custom Column error: Token Eof expected

[Frank J]

I've got a formula in Power Query I'm trying to use to read another column and assign a number for each quarter based on month. I can't figure out what's wrong, any help is appreciated.


=if [Month]="Jan" then 1 else [Month]="Feb" then 1 else [Month]="Mar" then 1 else [Month]="Apr" then 2 else [Month]="May" then 2 else [Month]="Jun" then 2 else [Month]="Jul" then 3 else [Month]="Aug" then 3 else [Month]="Sep" then 3 else [Month]="Oct" then 4 else [Month]="Nov" then 4 [Month]="Dec" then 4 else null

 

[Alex Blakenburg]

Are you actually importing a field called [Month] ?
If your "Month" is based on a date field and given that your quarters are calendar quarters it would be much cleaner to use the quarter function.

The formula will look something like this:-

= Table.AddColumn(#"Removed Columns", "Quarter", each Date.QuarterOfYear([Invoice_Date]), Int64.Type)

Generated via the menu as follows:

 

[Frank J]

Are you actually importing a field called [Month] ?
If your "Month" is based on a date field and given that your quarters are calendar quarters it would be much cleaner to use the quarter function.

The formula will look something like this:-

= Table.AddColumn(#"Removed Columns", "Quarter", each Date.QuarterOfYear([Invoice_Date]), Int64.Type)

Generated via the menu as follows:

View attachment 40664

That worked perfectly, thx so much. I'm new to this and self taught.

 

[Frank J]

Are you actually importing a field called [Month] ?
If your "Month" is based on a date field and given that your quarters are calendar quarters it would be much cleaner to use the quarter function.

The formula will look something like this:-

= Table.AddColumn(#"Removed Columns", "Quarter", each Date.QuarterOfYear([Invoice_Date]), Int64.Type)

Generated via the menu as follows:

View attachment 40664

I have another now giving me the same error for a different reason, it's hung up on the first "Roulette". The Game Type is an added column from the imported Asset Number and using Text.Start pulling out the first 2 letters. I'm trying to categorize all the different types to make another filter column for later use, after I create this column I'll then make another to further refine the game locations by joining the Category with the Area.

Game Category:

=if [Game Type]="MB" then "Baccarat" else [Game Type]="RW" then "Roulette" else [Game Type]="RT" then "Roulette" else [Game Type]="CR" then "Craps" else [Game Type]="CC" then "Craps" else [Game Type]="BJ" then "Blackjack" else [Game Type]="BP" then "Blackjack" else [Game Type]="JP" then "Blackjack" else [Game Type]="DD" then "Double Deck" else [Game Type]="SD" then "Blackjack" [Game Type]="PJ" then "Blackjack" else [Game Type]="JS" then "Blackjack" else [Game Type]="HH" then "Novelty" then [Game Type]="HC" then "Novelty" else [Game Type]="TC" then "Novelty" else [Game Type]="TP" then "Novelty" else [Game Type]="PG" then "Novelty" else [Game Type]="BS" then "Novelty" [Game Type]="RP" then "Novelty" else [Game Type]="RF" then "Novelty else null

 

[Alex Blakenburg]

In both Excel and Power Query it is bad practice to hard code a whole heap of alternatives into an "if statement". It makes the formula hard to read and high maintenance.
The minute you see an if statement like that you should immediately be thinking of using a lookup table.
In Excel you would then use vlookup, xlookup or index/match.
In Power Query, you would pull in the lookup table into PQ as a connection only query. Select your main query, merge it with the lookup query by doing a left join to the lookup table, using your Game Type field to link the 2 tables.
One of the significant benefits of using Query tools such as PQ, it the ability to join information from multiple sources ie do lookups. It is much better at doing lookup than Excel is and can easily do lookups requiring multiple fields to do the lookup. (Even the Data Model only does single field lookups)

 

[Frank J]

In both Excel and Power Query it is bad practice to hard code a whole heap of alternatives into an "if statement". It makes the formula hard to read and high maintenance.
The minute you see an if statement like that you should immediately be thinking of using a lookup table.
In Excel you would then use vlookup, xlookup or index/match.
In Power Query, you would pull in the lookup table into PQ as a connection only query. Select your main query, merge it with the lookup query by doing a left join to the lookup table, using your Game Type field to link the 2 tables.
One of the significant benefits of using Query tools such as PQ, it the ability to join information from multiple sources ie do lookups. It is much better at doing lookup than Excel is and can easily do lookups requiring multiple fields to do the lookup. (Even the Data Model only does single field lookups)

So I should just start a new sheet and make it a table with those statements in a column format 2 wide, then import it to the PQ as a list? I get the connection only part and what you mean but to bring it in...

 

[Frank J]

So I should just start a new sheet and make it a table with those statements in a column format 2 wide, then import it to the PQ as a list? I get the connection only part and what you mean but to bring it in...

I got it to work, thanks so much. this really will save me a lot of time as I need to do it 12 times.

 

[Alex Blakenburg]

Glad you were able to work it out and I appreciate you letting me know.
Sorry I couldn't engage in real time but I am in Australia and at that point it was the middle of the night here.

I would just like to address this statement though.

I need to do it 12 times.

Typically in PQ

• You finish up with a workbook that you just need to refresh when you get new data
  (ideally keeping a backup / archive version of the previous version)
• If the 12 files already exist then you generally use PQs ability to import all the files in a folder at the one time using its combine function.

I assume you worked out that you had to do something like the below.
(if Game Code wasn't unique and you needed to do the lookup on more than just 1 field, in PQ that is just a few more clicks in this screen while in Excel the formula would quickly get more complicated)