• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Power Query based Pivot table

K

Karanbir Soin

Member
I have data as two tables in NFO_Contract_Note.xlsx . Table8 is related to ContractNoteDetails on the date field.
I have done the get and transform data on these two tables. In the final pivot report I need Date, Obligation Pay In/ Pay Out, Brokerage & Exchange Fee & Taxes and a column that is subtraction of Obligation Pay In/ Pay Out and Brokerage & Exchange Fee & Taxes. I am not able to add this difference column in the Get and transform part in the power query editor....
Appreciate you folks taking time to ponder on this...
 

Attachments

Hi, The column is what I want. Please share with me how you added this column in one of the tables in the query. This is my first attempt at using power query. Thanks @p45cal...
 
Since you added both the ContractNoteDetails and the Table8 (3) queries to the data model, I went into that (via the ribbon's Power Pivot tab or the Data tab under Manage Data Model) and added a column called Difference to the ContractNoteDetails with the formula:
='Table8 3'[Sum of Obligation Pay In / Pay Out]-[Brokerage & Exchange Fee & Taxes]
Refreshing the pivot table brought the new column up in the Fields pane for the pivot.

A reminder; I don't know what I'm doing.
 
Can you please send me the data model file that you worked on. I am getting error on using this formula in the power query editor while adding a custom column in ContractNoteDetails
 
Karanbir Soin said:
Can you please send me the data model file that you worked on. I am getting error on using this formula in the power query editor while adding a custom column in ContractNoteDetails
Click to expand...
This is not done in Power Query but in Manage Data Model. It's all in the attachment to msg#2.
77549
 
Last edited:
I too am on O365 and it works fine. I've just checked the file attached to msg#2 and all is in order.
You do realise, that in Power Query, you will have to adjust the path to the appropriate workbook/folder, because obviously the file(s) won't be in the same place on our respective sytems. Yours was C:\Users\kssoi\OneDrive\Desktop\Desktop7June2020\itaxRelated\AY2022-23\Kssoin\NFO\, (I'm not going to set up the same directory structure on my machine).
What's the error? - 'not working' isn't very informative.
 
You must log in or register to reply here.
Back
Top