Thanks Thanks:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Problem with spaces in a Power Query ?

  1. #1
    New Member
    Join Date
    Jul 2014
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Problem with spaces in a Power Query ?


    Problem with spaces in a Power Query ?
    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) =>
    if (Product name) = "Car" then"Motorised"
    else if (Product name) = "Motorbike" then"Motorised"
    else if (Product name) = "Bicycle" then"Manual"
    else "Other"
    in Category
    Last edited by Keyboard; Feb 19th, 2018 at 08:52 AM.

  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    73,724
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Problem with spaces in a Power Query ?

    Try enclosing the field name in square brackets.
    If posting code please use code tags.

  3. #3
    New Member
    Join Date
    Jul 2014
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Problem with spaces in a Power Query ?

    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

  4. #4
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    73,724
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Problem with spaces in a Power Query ?

    How exactly are you setting the function up?

    Are you inserting a Custom Column?
    If posting code please use code tags.

  5. #5
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    31,840
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Problem with spaces in a Power Query ?

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

  6. #6
    New Member
    Join Date
    Jul 2014
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Problem with spaces in a Power Query ?

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

  7. #7
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    31,840
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Problem with spaces in a Power Query ?

    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

  8. #8
    New Member
    Join Date
    Jul 2014
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Problem with spaces in a Power Query ?

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

  9. #9
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    31,840
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Problem with spaces in a Power Query ?

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

  10. #10
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    73,724
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Problem with spaces in a Power Query ?

    Rory

    Does the code you posted work if you replace the underscore with a space?
    If posting code please use code tags.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •