# Problem with spaces in a Power Query ?



## Keyboard (Feb 19, 2018)

Hi,

I am struggling with Power Query in Excel.

I am trying to create a function to use in the spreadsheet. 

My spreadsheet contains column headers with spaces in and therefore my query contains the same. 

The problem seems to be that my query has spaces in there - if I change (Product name) to (Productname) then it works fine but I don't understand why this is and I would rather keep columns in the query named the same as the columns in the spreadsheet. Can I rewrite the below query so that it works by keeping the spaces in the column names?

let Category = (Product name) =><o></o>
<o></o>if (Product name) = "Car" then"Motorised"<o></o>
else if (Product name) = "Motorbike" then"Motorised"<o></o>
else if (Product name) = "Bicycle" then"Manual"<o></o>
else "Other"<o></o>
in Category<o></o>


----------



## Norie (Feb 19, 2018)

Try enclosing the field name in square brackets.


----------



## Keyboard (Feb 19, 2018)

Thank-you for your reply, if I alter the query using square brackets as below, I get the error message: Expression.SyntaxError: Token Comma expected

let Category = [Product name] =>
if [Product name] = "Car" then"Motorised"
else if [Product name] = "Motorbike" then"Motorised"
else if [Product name] = "Bicycle" then"Manual"
else "Other"
in Category


----------



## Norie (Feb 19, 2018)

How exactly are you setting the function up?

Are you inserting a Custom Column?


----------



## RoryA (Feb 19, 2018)

What exactly are you trying to do with this, and what is your full M code?


----------



## Keyboard (Feb 19, 2018)

I am setting up the Function by going to New query - from other sources - blank query and then I am typing in the code I have given.

Then to use the code I am going to insert a custom column and then type in:

=Category ([Product name])

The objective is to create a custom column which is populated with the categories as per the query (Motorised, manual, and other).


----------



## RoryA (Feb 19, 2018)

I think your code should look more like this:


```
(#"Product_name") =>
let Category = 
if #"Product_name" = "Car" then "Motorised"
else if #"Product_name" = "Motorbike" then "Motorised"
else if #"Product_name" = "Bicycle" then "Manual"
else "Other"
in Category
```


----------



## Keyboard (Feb 20, 2018)

Thanks a lot, that worked for me. Do I need to use a # whenever I reference a column name in M ?


----------



## RoryA (Feb 20, 2018)

No, it's only because your _variable_ name had spaces in it (which I inadvertently changed to underscores in the code above while testing!).


----------



## Norie (Feb 20, 2018)

Rory

Does the code you posted work if you replace the underscore with a space?


----------



## Keyboard (Feb 19, 2018)

Hi,

I am struggling with Power Query in Excel.

I am trying to create a function to use in the spreadsheet. 

My spreadsheet contains column headers with spaces in and therefore my query contains the same. 

The problem seems to be that my query has spaces in there - if I change (Product name) to (Productname) then it works fine but I don't understand why this is and I would rather keep columns in the query named the same as the columns in the spreadsheet. Can I rewrite the below query so that it works by keeping the spaces in the column names?

let Category = (Product name) =><o></o>
<o></o>if (Product name) = "Car" then"Motorised"<o></o>
else if (Product name) = "Motorbike" then"Motorised"<o></o>
else if (Product name) = "Bicycle" then"Manual"<o></o>
else "Other"<o></o>
in Category<o></o>


----------



## RoryA (Feb 20, 2018)

Yes, it does.


----------



## Norie (Feb 20, 2018)

Just realised my mistake, I was entering it directly to the formula bar and the quotes were being doubled up and other weird stuff.

Works fine when I enter it using the Advanced Editor.


----------

