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?
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
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.
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.
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.
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.
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.
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.
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
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
Bookmarks