Forum Discussion
Summing (SUMIFS) with a Pivot Table and Manual Table. Easier Method?
I would suggest you spend a few minutes reading https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_win10-mso_2010/power-query-get-transform-merge-join-append/83252b4a-785d-4e10-91dd-c887df283a65?tm=1589777580246 as nowhere in what I shared there's a merge between the 2 tables/queries, hence your questions
Assuming you used the same tables names in Excel (RawData & Inputs)
And you can select Inputs as First table and RawData as Second table, that doesn't matter in this scenario
Let's go 1st step at a time if you don't mind
1/ Nowhere I used Power Pivot but Power Query
2/ Look at sheet Inputs in the file I shared. I made a copy/paste of the relevant columns and rows (from your manual inputs in sheet Working Tab) and formatted the copied data as a Table that I named Inputs
3/ I "loaded" the existing table RawData in Power Query and did the same with the newly created Inputs table
4/ In Power Query I did a couple of transformations so the Inputs table looks the same as the RawData table
5/ I combined/appended tables RawData & Inputs, then on exiting the Power Query Editor I chose option Pivot Table Report
So, above steps 3-5 are done in/with Power Query. Within Excel: Go to Data tab > Queries & Connections (this will open the corresponding pane on the right and you'll see:
> Double-click on i.e. Inputs and the Power Query Editor will open
And please, let's stay on the same page/picture => Use the file attached to this reply, it's an easier version of the 1st I posted
1) Do I make the merge as new or append as new? Which one?
2) Did you merge/append the raw data to the input or vice versa? I did both ways (after cleaning and matching columns) and only pulls the data in that table.
3) What was the primary key/foreign key? I chose company and didn't get what I needed.
4) What type of join? I did default Left Join.
Other than making the merge queries that's all I need.
Thank you.
- Glad I could help + Thanks for posting back
And don't apologize, we all started from scratch
Nice day... Thank you for the assistance. And thank you for providing me the details. I will go through this is detail.
Thank you for all the help. This fixed it. I apologize for my ignorance and thank you for showing me and you patience.I would suggest you spend a few minutes reading https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_win10-mso_2010/power-query-get-transform-merge-join-append/83252b4a-785d-4e10-91dd-c887df283a65?tm=1589777580246 as nowhere in what I shared there's a merge between the 2 tables/queries, hence your questions
Assuming you used the same tables names in Excel (RawData & Inputs)
And you can select Inputs as First table and RawData as Second table, that doesn't matter in this scenario