+ Reply to Thread
Results 1 to 15 of 15

Power Query not updating New Item

  1. #1
    Forum Contributor
    Join Date
    03-12-2012
    Location
    Singapore
    MS-Off Ver
    MS 365
    Posts
    532

    Power Query not updating New Item

    i have Pivot a Column in my query. Initally, there is 3 unique item in the Column, hence my Pivot show 3 additional row. However, if i add in new item, my query wont auto pivot that Additional Item. Why is this so?

    Please see example for better understanding
    Attached Files Attached Files

  2. #2
    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
    79,369

    Re: Power Query not updating New Item

    It does if you remember to refresh the query.

    AliGW on MS365 Insider (Windows) 64 bit

    A
    B
    C
    D
    E
    F
    G
    1
    Number Table1 Dr.Inventory Table1 Dr.PO Table1 Dr.Admin Fee Table1 Dr.GST Table1 Cr.PO Table1 Cr.Trade Payable
    2
    A1-11
    100,000.00
    100,000.00
    100.00
    7.00
    100,000.00
    100,107.00
    3
    A1-12
    12,100.00
    12,100.00
    Sheet: Merge

    AliGW on MS365 Insider (Windows) 64 bit

    A
    B
    C
    D
    E
    10
    A1-12 PO
    2,000.00
    Sheet: Data

    AliGW on MS365 Insider (Windows) 64 bit

    A
    B
    C
    D
    E
    F
    G
    1
    Number Table1 Dr.Inventory Table1 Dr.PO Table1 Dr.Admin Fee Table1 Dr.GST Table1 Cr.PO Table1 Cr.Trade Payable
    2
    A1-11
    100,000.00
    100,000.00
    100.00
    7.00
    100,000.00
    100,107.00
    3
    A1-12
    12,100.00
    2,000.00
    12,100.00
    Sheet: Merge
    Attached Files Attached Files
    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.

  3. #3
    Forum Contributor
    Join Date
    03-12-2012
    Location
    Singapore
    MS-Off Ver
    MS 365
    Posts
    532

    Re: Power Query not updating New Item

    Hi, i dun mean additional Row. I mean if i add a new item in Column B or Column D
    Example your A1-13, my column B is lets say COGS instead of Inventory. When i refresh in my Merge.
    It wont create a Column for COGS. Instead my Inventory figure just become blank.

  4. #4
    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
    79,369

    Re: Power Query not updating New Item

    OK - how about sharing a sample workbook that actually SHOWS the problem???

    It would be better if you explained what you are trying to do. Your workbook has four queries - it probably needs only one, but I don't really understand your expectations.
    Last edited by AliGW; 01-27-2022 at 05:41 AM.

  5. #5
    Forum Contributor
    Join Date
    03-12-2012
    Location
    Singapore
    MS-Off Ver
    MS 365
    Posts
    532

    Re: Power Query not updating New Item

    Hi, please see attached. I create this query when i am doing A1-12. Hence my pivot column only show when the Column B is Inventory, PO, Admin Fee or GST.
    And my pivot column onli works when Column D is PO or trade payable. If i create new line with Column B is COGS, and Column D is Bank.
    My merge table wont auto populate the Pivot Column.
    Attached Files Attached Files

  6. #6
    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
    79,369

    Re: Power Query not updating New Item

    OK - what you haven't done is explained what you are trying to do. I don't believe the way you are doing what you are doing is either correct or efficient, but I need to understand your reasoning if I (or anyone else) am (is) to help here.

  7. #7
    Forum Contributor
    Join Date
    03-12-2012
    Location
    Singapore
    MS-Off Ver
    MS 365
    Posts
    532

    Re: Power Query not updating New Item

    Sorry, i am trying to make one Row per transaction instead of many rows. Becoz in my real file, one Transaction has around 8-10 rows. Making it veri hard to see. Hence trying to summarise it in 1 row.
    N from the particular row, i can c how much is Being Dr and Cr from each account.
    Would appreciate if you are able to advice how can i acheieve it as i am not so familiar with Query.

  8. #8
    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
    79,369

    Re: Power Query not updating New Item

    OK - I understand now.

    I will have a look later (tied up at the moment - sorry).

  9. #9
    Forum Contributor
    Join Date
    03-12-2012
    Location
    Singapore
    MS-Off Ver
    MS 365
    Posts
    532

    Re: Power Query not updating New Item

    Hi, thanks for your help. Hope you can advice when you are free. Had search online also, but unable to find the answer...

  10. #10
    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
    79,369

    Re: Power Query not updating New Item

    Not today. I have put out a call for help to see if someone else can step in.

  11. #11
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Power Query not updating New Item

    Please try

    PHP Code: 
    let
        Source 
    Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        
    Grouped Table.Group(Source, {"Number"}, {{"R"each let c Table.ToColumns(_in Record.FromList(List.RemoveNulls(c{2}&c{4}),
            List.
    RemoveNulls(List.Transform(c{1},each "Dr."&_)&  List.Transform(c{3},each "Cr."&_))) }}),
        
    Expanded Table.ExpandRecordColumn(Grouped"R", List.Transform(List.Distinct(List.RemoveNulls(Source[Dr GL])),each "Dr."&)& List.Transform(List.Distinct(List.RemoveNulls(Source[Cr GL])),each "Cr."&))
    in
        Expanded 
    Attached Files Attached Files

  12. #12
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Power Query not updating New Item

    another, perhaps more basic approach, is to store the data in a table, then setup the pivot such that TYPE is on the horizontal, and AMT is Data Field
    (the Pivot will auto group any balances that appear with same "number" and "type")

    it also means you don't need to amend your pivot every time a new "type" is added -- which the matrix storage would necessitate

    Please Login or Register  to view this content.

  13. #13
    Forum Contributor
    Join Date
    03-12-2012
    Location
    Singapore
    MS-Off Ver
    MS 365
    Posts
    532

    Re: Power Query not updating New Item

    Hi, may i know how to paste? I am not that good at query.

  14. #14
    Forum Contributor
    Join Date
    03-12-2012
    Location
    Singapore
    MS-Off Ver
    MS 365
    Posts
    532

    Re: Power Query not updating New Item

    Quote Originally Posted by XLent View Post
    another, perhaps more basic approach, is to store the data in a table, then setup the pivot such that TYPE is on the horizontal, and AMT is Data Field
    (the Pivot will auto group any balances that appear with same "number" and "type")

    it also means you don't need to amend your pivot every time a new "type" is added -- which the matrix storage would necessitate

    Please Login or Register  to view this content.


    Are you able to show example? Seems like there is no default function in Query to do that.

  15. #15
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Power Query not updating New Item

    refer Sheet2 in the attached - and final query in PQ, which if this works for you, means all other queries can be removed
    Attached Files Attached Files

+ 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 Table Automatically not updating
    By John521520 in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 01-07-2022, 05:58 AM
  2. Access Query not Updating in Power Pivot
    By mburkett in forum Excel General
    Replies: 0
    Last Post: 06-24-2021, 09:17 AM
  3. updating a power query
    By breathin in forum Excel General
    Replies: 2
    Last Post: 09-11-2020, 09:29 AM
  4. Updating a table with new data in power query
    By SueBristow in forum Excel General
    Replies: 0
    Last Post: 04-02-2020, 03:53 PM
  5. Updating a Parameter through VBA in Power Query
    By RAC81_12 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-24-2020, 09:49 AM
  6. Power Query: Updating a query based on changing tables
    By cheesehead101 in forum Excel General
    Replies: 1
    Last Post: 12-16-2019, 06:54 PM
  7. Replies: 0
    Last Post: 04-05-2018, 01:16 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1