+ Reply to Thread
Results 1 to 10 of 10

Adding Column in Power Query

  1. 06-28-2018, 07:48 AM #1
    fuerte
    fuerte is offline
    Registered User
    Join Date
    02-14-2018
    Location
    Wisconsin, USA
    MS-Off Ver
    2016
    Posts
    35

    Adding Column in Power Query

    Hi All,

    I am trying to add a column in Power Query using an if statement and a relative reference.

    My Formula for the query is:

    = Table.AddColumn(#"Added Index", "Custom", each if [Action] = "Release (Not Ready) " then {[Index]+1} [Time] else 0)

    This spits out an error of:

    Expression.Error: We cannot apply field access to the type List.
    Details:
    Value=List
    Key=Time

    I know I am like half way to getting this column to work correctly I just can't find any info on this error. Can anyone help with this?
    Reply With QuoteRegister To Reply
  2. 06-28-2018, 08:59 AM #2
    Bob Phillips
    Bob Phillips is offline
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: Adding Column in Power Query

    What exactly are you trying to set it to when you add the statement

    {[Index]+1} [Time]
    Reply With QuoteRegister To Reply
  3. 06-28-2018, 09:35 AM #3
    fuerte
    fuerte is offline
    Registered User
    Join Date
    02-14-2018
    Location
    Wisconsin, USA
    MS-Off Ver
    2016
    Posts
    35

    Re: Adding Column in Power Query

    I am trying to return the value that is in the next row down and in the column [Time]
    Reply With QuoteRegister To Reply
  4. 06-28-2018, 11:15 AM #4
    Bob Phillips
    Bob Phillips is offline
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: Adding Column in Power Query

    It might be possible in one column, but I got over the list issue by creating two custom columns then deleting one

    Please Login or Register  to view this content.
    Last edited by Bob Phillips; 06-28-2018 at 04:33 PM.
    Reply With QuoteRegister To Reply
  5. 06-28-2018, 11:18 AM #5
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,778

    Re: Adding Column in Power Query

    This is not about VBA or macros, so I've moved it to the formulas and functions section. Unfortunately we do notg yet have a section for Power Query and Power BI.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  6. 06-28-2018, 11:20 AM #6
    Bob Phillips
    Bob Phillips is offline
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: Adding Column in Power Query

    That is a shame, maybe time to start one
    Last edited by AliGW; 06-28-2018 at 11:24 AM. Reason: Quotation not needed.
    Reply With QuoteRegister To Reply
  7. 06-28-2018, 11:24 AM #7
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,778

    Re: Adding Column in Power Query

    It is, and we've asked before - I've just ressurrected a thread in the suggestions section - care to add your voice to it?
    Reply With QuoteRegister To Reply
  8. 06-28-2018, 11:51 AM #8
    fuerte
    fuerte is offline
    Registered User
    Join Date
    02-14-2018
    Location
    Wisconsin, USA
    MS-Off Ver
    2016
    Posts
    35

    Re: Adding Column in Power Query

    I have tried to add your script to my advanced editor in Power Query but it does not seem to take.

    My script for power query so far is: 
    Please Login or Register  to view this content.
    #"Added Conditional Column"

    I am not quite sure where to add the script you provided as I don't normally do stuff in the advanced editor.
    Last edited by fuerte; 06-28-2018 at 11:57 AM.
    Reply With QuoteRegister To Reply
  9. 06-28-2018, 11:58 AM #9
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,778

    Re: Adding Column in Power Query

    Fuerte - you must use code tags when quoting M code. I've done it for you this time.
    Reply With QuoteRegister To Reply
  10. 06-28-2018, 12:39 PM #10
    CK76
    CK76 is offline
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,883

    Re: Adding Column in Power Query

    To use INDEX, to reference next row would be something like...
    Please Login or Register  to view this content.
    So in your case...
    Please Login or Register  to view this content.
    As well, this could throw an error at end of column as there's no row to reference beyond last row.
    To avoid it, you can use try.. otherwise.
    Please Login or Register  to view this content.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Modifications in Power Query not updating in Power BI Service linked via Local Drive Excel
    By ibuhary in forum Excel General
    Replies: 0
    Last Post: 04-05-2018, 01:16 AM
  2. [SOLVED] Insert a date value as a Custom Column in Power Query
    By heytherejem in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 03-28-2018, 12:42 PM
  3. Power Query and adding a column changing dates
    By ammartino44 in forum Excel General
    Replies: 0
    Last Post: 01-09-2018, 05:24 AM
  4. Power Query - add column with Table name
    By zico8 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 05-11-2017, 12:58 AM
  5. [SOLVED] Adding Missing Dates To A .CSV File Via Power Query?..
    By Ourkid123uk in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-04-2017, 03:54 PM
  6. Help with Power Query Custom Column Formula
    By travis.cook21 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-16-2016, 01:45 PM
  7. Adding data to the end of a table with Power Query
    By afila in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-14-2016, 08:29 AM

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1