SOLVED

queries and merging

%3CLINGO-SUB%20id%3D%22lingo-sub-1506166%22%20slang%3D%22en-US%22%3Equeries%20and%20merging%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1506166%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Team%2C%20I%20am%20supposed%20to%20compare%20data%20from%20sold%20by%20category%20and%20manufacturer%2C%20this%20data%20is%20stored%20in%20two%20different%20tables%3A%20Purchases%20and%20Products...%20I%20am%20stuck%20with%20what%20to%20do%20and%20how%20to%20link%20them...%20here%20are%20the%20instructions%3A%3C%2FP%3E%3COL%3E%3CLI%3EGo%20to%20the%20%3CEM%3EManufacturers%3C%2FEM%3E%20worksheet.%20Jacob%20wants%20to%20compare%20products%20sold%20by%20category%20and%20manufacturer.%20This%20data%20is%20stored%20in%20the%20Purchases%20and%20Products%20tables.%3CBR%20%2F%3ECreate%20a%20PivotTable%20as%20follows%20that%20provides%20the%20products%20sold%20by%20manufacturer%20information%20for%20Jacob%3A%3COL%3E%3CLI%3EIn%20cell%20A2%2C%20use%20Power%20Pivot%20to%20insert%20a%20PivotTable%20in%20the%20%3CEM%3EManufacturers%3C%2FEM%3E%20worksheet.%3C%2FLI%3E%3CLI%3EUse%20the%20following%20fields%20in%20the%20PivotTable%3A%3CBR%20%2F%3E%C2%B7%20%3CSTRONG%3EManufacturer%3C%2FSTRONG%3E%20field%20from%20the%20Products%20table%20for%20the%20row%20headings%3CBR%20%2F%3E%C2%B7%20%3CSTRONG%3ECategory%3C%2FSTRONG%3E%20field%20from%20the%20Products%20table%20for%20the%20column%20headings%3CBR%20%2F%3E%C2%B7%20%3CSTRONG%3EItemQty%3C%2FSTRONG%3E%20field%20from%20the%20Purchases%20table%20for%20the%20values%3C%2FLI%3E%3C%2FOL%3E%3C%2FLI%3E%3CLI%3EIn%20order%20to%20relate%20the%20data%20in%20the%20Products%20and%20Purchases%20tables%20to%20make%20a%20proper%20comparison%2C%20use%20the%20Power%20Pivot%20window%20to%20create%20a%20relationship%20between%20the%20Purchases%20and%20Products%20tables%20based%20on%20the%20ItemID%20field.%3C%2FLI%3E%3C%2FOL%3E%3CP%3EWhen%20I%20go%20to%20merge%20the%20two%20queries%20I%20can%20only%20merge%20the%20columns%20that%20are%20alike%20thus%20removing%20the%20other%20data%20that%20I%20need%2C%20so%20I%20am%20confused%20how%20I%20merge%20the%20two%20sheets%20and%20get%20all%20of%20the%20other%20relevant%20data%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EObviously%20the%20instructions%20tell%20me%20to%20use%20the%20power%20pivot%20but%20I%20am%20not%20entirely%20sure%20how%20to%20use%20that%20method%20when%20I%20don't%20have%20the%20data%20in%20the%20workbook%20so%20I%20cannot%20reference%20it%20or%20detect%20to%20see%20possible%20data%20intersections%2C%20please%20help...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eall%20files%20attached%20-%20sheets%203%26amp%3B4%20contain%20the%20query%20data%2C%20but%20I%20am%20supposed%20to%20go%20off%20the%20query%20not%20the%20chart%20form%2C%20but%20for%20some%20reason%20the%20Microsoft%20access%20file%20would%20not%20upload%20with%20this%20post%20so%20I%20put%20the%20data%20in%20the%20spreadsheet!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethx%20-%20G%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1506166%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Contributor

Hi Team, I am supposed to compare data from sold by category and manufacturer, this data is stored in two different tables: Purchases and Products... I am stuck with what to do and how to link them... here are the instructions:

  1. Go to the Manufacturers worksheet. Jacob wants to compare products sold by category and manufacturer. This data is stored in the Purchases and Products tables.
    Create a PivotTable as follows that provides the products sold by manufacturer information for Jacob:
    1. In cell A2, use Power Pivot to insert a PivotTable in the Manufacturers worksheet.
    2. Use the following fields in the PivotTable:
      · Manufacturer field from the Products table for the row headings
      · Category field from the Products table for the column headings
      · ItemQty field from the Purchases table for the values
  2. In order to relate the data in the Products and Purchases tables to make a proper comparison, use the Power Pivot window to create a relationship between the Purchases and Products tables based on the ItemID field.

When I go to merge the two queries I can only merge the columns that are alike thus removing the other data that I need, so I am confused how I merge the two sheets and get all of the other relevant data

 

Obviously the instructions tell me to use the power pivot but I am not entirely sure how to use that method when I don't have the data in the workbook so I cannot reference it or detect to see possible data intersections, please help...

 

all files attached - sheets 3&4 contain the query data, but I am supposed to go off the query not the chart form, but for some reason the Microsoft access file would not upload with this post so I put the data in the spreadsheet!

 

thx - G

5 Replies

forgot to re-attach the updated spreadsheet with the data... here it is...

best response confirmed by gltech (Occasional Contributor)
Solution

@gltech 

You don't need to merge queries. Load tables to data model and follow the instruction literally, starting with Section 2 (create relationships).

@Sergei Baklan Ok, I understand that part, but how do I load the two separate tables into a data model? I know how to load one, but in order to load both do I need to paste both data tables onto the same sheet and use that?

@Sergei Baklan nevermind! I got it! thank you for your help!

@gltech 

If you Power Query the data, no need to load table into worksheet. You shall have two queries for two tables. In queries pane right click on query name, select Load To and choose correct option.