Power Query Merge Help

New Contributor

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:

Max_Petti_0-1650564556952.png


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:

Max_Petti_1-1650564828675.png

*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:

Max_Petti_0-1650566113151.png

-------------------------------------------------------------------------------------

*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):

Max_Petti_0-1650592084216.png

 

 

Max_Petti_1-1650591772297.png

 

*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,

Max_Petti_1-1650632845863.png

*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:

Max_Petti_3-1650633446415.png


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:

Max_Petti_6-1650634178638.png

Table example:

Max_Petti_7-1650634322813.png

 

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.

Max_Petti_4-1650633706642.png

data table example:

Max_Petti_5-1650634083732.png

 

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):

Max_Petti_8-1650634472082.png


Thank You,
Any ideas?



4 Replies

@Max_Petti Forgive me for being blunt. No, still not clear. Let's focus on the last two screenshots and assume that this is all the date. How would you want the end result to look like? And why? With what logic? But perhaps even better to show us data that actually matches the real data (with confidential information removed, of course) rather that just a "more accurate representation" of the data.

@Riny_van_Eekelen  Again, spreadsheet A consists of rows of data in which each row of data correlates to a specific record; spreadsheet B is being leveraged to capture addition items in excess that belong to the unique records as identified on spreadsheet A - a single client can have multiple rows of records with varying characteristics, hence the provided examples above. Please elaborate further as to exactly what is unclear.

I have attempted to update the post with additional context - please let me know if this is more sufficient in determining a possible resolution. in further elaborating upon the data, what we're seeing here is a collection of clients that a part of a financial advisor's book of business.  There may be instances of the same client showing up on mutiple rows, with the same type of main/feature forms being requested - this is due to clients being able to have multiple main forms.  Example, John Smith might have 3 accounts with an advisor; in gathering the data for this client, he would have 3 rows of data, with the main form type denoting which type of account to be opened.  The first 6 feature forms are listed in the manner referenced above (transposed) due to each account/row of data being able to only have 1 of each of those first 6 features.  In addition, John Smith's 3 accounts (reflected as 3 rows of data on the first spreadsheet, and uniquely identified by App #) may have multiples of each feature form 7-12, and this is why the data is ultimately split and captured between two tables/worksheets.

 

 Appreciate you taking the time to look here, I am only trying to determine whether this type of dynamic data merge is possible

@Max_Petti Really appreciate your efforts to clarify your problem, but I really have no clue what you are trying to achieve. Perhaps upload or share your file so that we can work with real data. Otherwise I'll give up, sorry.

No worries, thank you for your attempts. The goal was to consolidate the 2 feature form pivot tables into 1 with a full count of all feature forms broken out by main form type. Sharing the data is not an option unfortunately due to compliance factors.