Power Query based Pivot table

[Karanbir Soin]

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

 

[p45cal]

I don't know what I'm doing. Does Sheet3 pivot last column of the attached get close?

 

[Karanbir Soin]

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

 

[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.

 

[Karanbir Soin]

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

 

[p45cal]

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

This is not done in Power Query but in Manage Data Model. It's all in the attachment to msg#2.

 

[Karanbir Soin]

@p45cal I am using office 365 here in data ribbon the data model is not working

 

[p45cal]

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.

 

[Karanbir Soin]

I was trying to work this formula in my set of files that were attached in this post. Let me try again....

 

[Karanbir Soin]

Hi @p45cal was able to get my Power Pivot add in working.
Thanks for your patience....