+ Reply to Thread
Results 1 to 13 of 13

Power Pivot does not load new column added in Power query

  1. #1
    Forum Contributor
    Join Date
    06-01-2014
    Location
    Riyadh, Saudi Arabia
    MS-Off Ver
    Microsoft 365
    Posts
    224

    Question Power Pivot does not load new column added in Power query

    Hi All,

    I have a Power query connection only loading into power pivot. The model is source for many calculated columns and measures.

    I have done one change in the source power query data by adding a new column.

    Then I have copied the connection string from existing connections in Excel Worksheet and pasted in Power Pivot existing data connections strings so there is no problems in Data Refresh and it all works well.

    But The new column I added in Power Query doesn't show up in Power Pivot.

    I have tried to go in "Table Properties" and "Validate" the sql statement and also clicked "Design" to see if the sql statement inside is ok and then "Saved" there, but nothing seems to bring on the new column from power query.

    I have the following statement inside "Table Properties":
    SELECT * FROM [Booking details report]

    What am I missing here ?

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,209

    Re: Power Pivot does not load new column added in Power query

    Hi ibuhary,

    Perhaps the Table you are doing the Power Pivot on needs to be a Dynamic Named Range so when a new column is added it thinks it is part of the table????

    https://www.exceldemy.com/excel-dynamic-named-range/
    https://www.ozgrid.com/Excel/DynamicRanges.htm
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Contributor
    Join Date
    06-01-2014
    Location
    Riyadh, Saudi Arabia
    MS-Off Ver
    Microsoft 365
    Posts
    224

    Re: Power Pivot does not load new column added in Power query

    No my data sources don't come from any tables.

    My power query fetches data from another excel sheet and the data in the external excel workbook also doesn't have any tables.

    I just tried adding a new dummy row to the data to check if that is updating and yes its working fine. But the new column still doesn't refresh.

  4. #4
    Forum Contributor
    Join Date
    06-01-2014
    Location
    Riyadh, Saudi Arabia
    MS-Off Ver
    Microsoft 365
    Posts
    224

    Re: Power Pivot does not load new column added in Power query

    Is there an alternate way of telling power pivot to fetch all columns (instead of using " * " ) so that I can try to use the sql statements to try and manipulate to get this working.

  5. #5
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,209

    Re: Power Pivot does not load new column added in Power query

    You can use Dynamic Named Ranges on ranges that aren't tables. You don't need a table to use these DNRs.

  6. #6
    Forum Contributor
    Join Date
    06-01-2014
    Location
    Riyadh, Saudi Arabia
    MS-Off Ver
    Microsoft 365
    Posts
    224

    Re: Power Pivot does not load new column added in Power query

    Sorry, my issue is not with the source data.

    Its in power query to power pivot.

  7. #7
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,209

    Re: Power Pivot does not load new column added in Power query


  8. #8
    Forum Contributor
    Join Date
    06-01-2014
    Location
    Riyadh, Saudi Arabia
    MS-Off Ver
    Microsoft 365
    Posts
    224

    Re: Power Pivot does not load new column added in Power query

    I don't think you understood my issue well.

  9. #9
    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. 2501 (Windows 11 24H2 64-bit)
    Posts
    89,060

    Re: Power Pivot does not load new column added in Power query

    It's difficult for anyone to help without a sample workbook. Please provide one.
    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. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

  10. #10
    Forum Contributor
    Join Date
    06-01-2014
    Location
    Riyadh, Saudi Arabia
    MS-Off Ver
    Microsoft 365
    Posts
    224

    Re: Power Pivot does not load new column added in Power query

    I will try to upload a sample file, I need to get rid of confidential data.

    In the mean time to make it more easier to understand...

    When I created the model in Power Pivot I had for e.g. 10 columns coming from Power Query.

    Now I have 11 columns in Power Query including a new custom column.

    When I refresh Power Pivot the updates I see only for the existing 10 columns I am unable to bring in the new Column from Power Query into Power Pivot.

  11. #11
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,209

    Re: Power Pivot does not load new column added in Power query

    In this link below it claims you can't have an underline (or special character) in the new column head. Do you?
    https://community.powerbi.com/t5/Ser...fter/td-p/8061

  12. #12
    Forum Contributor
    Join Date
    06-01-2014
    Location
    Riyadh, Saudi Arabia
    MS-Off Ver
    Microsoft 365
    Posts
    224

    Re: Power Pivot does not load new column added in Power query

    i have managed to identify that the issue with any column not loading into power pivot from power query is due the column in power query not defining the format of the contents in the subject column.
    Meaning if the column had a mixed format of text & number (abc,123) format in power query then power pivot does not load it in the model.
    I hope this piece of information helps any other person who would run into a similar issue.
    Last edited by AliGW; 02-19-2019 at 03:53 AM.

  13. #13
    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. 2501 (Windows 11 24H2 64-bit)
    Posts
    89,060

    Re: Power Pivot does not load new column added in Power query

    Thanks for the update.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. POWER Query and Power Pivot Table !!
    By haitham.shop in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 08-06-2018, 04:09 PM
  2. Power Query and Power Pivot Table
    By haitham.shop in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 08-06-2018, 06:17 AM
  3. Power Query and Power Pivot Table !!
    By haitham.shop in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-06-2018, 06:15 AM
  4. POWER Query and Power Pivot Table !!
    By haitham.shop in forum Access Tables & Databases
    Replies: 0
    Last Post: 08-06-2018, 05:59 AM
  5. Sub-Forum for Excel Power Tools (Power Query, Power Pivot & Power BI)
    By chullan88 in forum Suggestions for Improvement
    Replies: 10
    Last Post: 06-28-2018, 02:25 PM
  6. error in power query & power pivot
    By Baldev Kumar in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 01-03-2018, 01:34 AM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1