Hello,
I have two data sets (connected with PQ) within a workbook, with each data set being contained on separate work sheets with differing numbers of rows/columns; I am wondering if it is possible to combine in the following manner: Spreadsheet A contains a unique identifier in Column A, followed by transposed columns of corresponding items with an indication of "Yes" to show correspondence, see example:
Spreadsheet B contains the same unique identifier in column A, followed by a column that effectively contains additional items (items D, E, F), with the caveat of these unique IDs being eligible to receive multiples of items D, E, F. see example:
*so unique ID 1 corresponds to having 1 of Item D, etc. and again as above in spreadsheet A, a "yes" denotes the same effective value of 1. In trying to sync the data, the goal would be to create a comprehensive list of how many items each unique ID has across both spreadsheets via a pivot table that is automatically refreshed by the power queries as new data comes in. I need to count the number of Yes' per item as it pertains to the unique id in spreadhseet A, and to count the number of items as it pertains to the unique id in spreadsheet B
I had previously attempted to unpivot the transposed items in SS A and merge the two queries from that point, but I would run into the issue of being limited to how many items were listed from SS B in the merged query. In my use case, i have a total of 9 items on spreadsheet A(i.e. A-I) and a remaining 6 items (J-O) on spreadsheet B, where some of the unique IDs correspond to 10 or more of a single item (resulting in 10+ rows for that unique ID).
Ideally the finished product would resemble something of the following within a pivot table:
-------------------------------------------------------------------------------------
*End goal Pivot Table Representation (update 4/22/22 9AM EST):
*edit/update - in the sake of additional clarity/comprehension, spreadsheet A may also contain instances of rows of data in which the unique identifier does not correspond to any items at all (there are other characteristics preceding the item columns which have been subsequently removed from the query; effectively spreadsheet A contains 90% of the scope of the data that I'm capturing and spreadsheet B is only being utilizing to accommodate these additional "items" in where there may be multiple per unique ID).
More accurate representation of actual data (spreadsheets A & B):
*unique id and request would both be the same between the two spreadsheets, except I cannot account for the additional count of the unique ID (multiple requests per ID) when it is listed more than once when considering the merge between the two sheets,
If anything is unclear, or further context is need please let me know.
*Edit (attempt to provide real data):
I have primary pivot table that reflect the count of "Main" forms for clients, broken out by "Main" form type; so there are 16 types of forms,
*the main form type being applied to the "rows" filter, and the count of apps being applied to "values."
the underlying data table for this pivot table looks as such:
i created a 2nd and 3rd table to count the number of accompanying feature forms that join these "Main" forms:
Pivot table for feature forms captured on spreadsheet B:
Table example:
Pivot table to count Feature forms listed within same table range as main forms:
*i used power query to unpivot the feature form columns in spreadsheet A's table and synced the data model to the pivot table.
data table example:
Looking to combine the data in power query for the two pivot tables reflecting the feature form counts to a single pivot table, if possible.
Example of updated end goal (as pivot table connected to data model):
Thank You,
Any ideas?
