Problem with spaces in a Power Query ?

Keyboard

New Member
Joined
Jul 15, 2014
Messages
7
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:p></o:p>
<o:p></o:p>if (Product name) = "Car" then"Motorised"<o:p></o:p>
else if (Product name) = "Motorbike" then"Motorised"<o:p></o:p>
else if (Product name) = "Bicycle" then"Manual"<o:p></o:p>
else "Other"<o:p></o:p>
in Category<o:p></o:p>
 
Last edited:

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Try enclosing the field name in square brackets.
 
Upvote 0
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
 
Upvote 0
How exactly are you setting the function up?

Are you inserting a Custom Column?
 
Upvote 0
What exactly are you trying to do with this, and what is your full M code?
 
Upvote 0
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).
 
Upvote 0
I think your code should look more like this:

Code:
(#"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
 
Upvote 0
No, it's only because your variable name had spaces in it (which I inadvertently changed to underscores in the code above while testing!).
 
Upvote 0
Rory

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

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top