User Profile
Max_Petti
Copper Contributor
Joined Apr 21, 2022
User Widgets
Recent Discussions
Re: Power Query Data Transposition Inquiry
Hey L.Z. Appreciate the response. Currently I am unable to send any actual files whilst working under certain compliance parameters. However, I will let you know if this changes in the event that I would be able to deliver a sample file. I did however copy the sample data below if that is something you're able to work with in the interim. Client Group Address City State 02210 Country Household Name Account # 1 1200 Flinstone Ave SAN ANTONIO TX 78209 US Smith Family 1234 2 518 Wizard's Way HOUSTON TX 77079 US Johnson Family 1235 2 519 Wizard's Way HOUSTON TX 77079 US Johnson Family 1236 2 520 Wizard's Way HOUSTON TX 77079 US Johnson Family 1237 4 903 Lover's Lane GALVESTON TX 77554 US Adams Family 1238 4 904 Lover's Lane GALVESTON TX 77554 US Adams Family 1239 4 905 Lover's Lane GALVESTON TX 77554 US Adams Family 1240 4 906 Lover's Lane GALVESTON TX 77554 US Adams Family 1241 Sincerely, MaxPower Query Data Transposition Inquiry
Hello, I am looking for suggestions on how to transform the following data set: to the following table: The data set deals with groups of clients (denoted in the first column); there can be more than 1 row of data per each group, and in some cases there are 15+ accounts/rows of data associated with a client group. I am hoping to combine all rows of data so each client group and corresponding address are reflected on a single row, with the corresponding account numbers (being the unique identifiers) being transposed. Is this possible to accomplish via power query? Sincerely, MaxRe: Power Query Merge Help
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 possiblePower Query Merge Help
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?
