Forum Discussion
Combine Multiple Rows with Blank Values
I'm creating a pivot table based on a large access database. In access, there's many rows per item number, and one column containing many attributes and another with the values of those attributes. In excel, I pivot the attributes/values columns (about 500 columns total) and it ends up looking like this, still with many rows but all attribute values blank except one. Is there an easy way to combine them? Like a Group By function but on a larger scale without having to make new columns? I'm not super familiar with M code, so am stumbling on one part chatgpt can't figure out when asked lol
| Item Number | Length (in) | Length (mm) | Width (in) | Width (mm) |
| 200 | (blank) | (blank) | (blank) | (blank) |
| .62 | (blank) | |||
| 866 | (blank) | (blank) | ||
| 34.1 | (blank) | (blank) | (blank) | |
| 201 | (blank) | (blank) | (blank) | (blank) |
| .63 | (blank) | |||
| 632 | (blank) | (blank) | ||
| 24.9 | (blank) | (blank) | (blank) | |
| 202 | (blank) | (blank) | (blank) | (blank) |
| .63 | (blank) | |||
| 434 | (blank) | (blank) | ||
| 17.1 | (blank) | (blank) | (blank) | |
| 203 | (blank) | (blank) | (blank) | (blank) |
| .63 | (blank) | |||
| 645 | (blank) | (blank) | ||
| 25.4 | (blank) | (blank) | (blank) | |
| 204 | (blank) | (blank) | (blank) | (blank) |
| .63 | (blank) | |||
| 315 | (blank) | (blank) | ||
| 12.4 | (blank) | (blank) | (blank) |
I am not 100% sure this is what you are looking for...
If you are starting with data like...You can use the Pivot Column (Table.Pivot) feature to end up with...
Select the "Attribute" column and click the 'Pivot Column' icon on the transform ribbon.
Use the value column in the 'Values Column' and select 'Don't Aggregate' in the Advanced options.Hope this gets you pointed in the right direction.
Using the example data from above, selecting the 'Item' column and grouping with 'All Rows' aggregation will get you...
There are no columns added to the nested tables. They are just the rows that share the Item number...
Item 200 as example...You can transform the nested columns if need to though you would need to use the Table.TransformColumns function as there is no UI feature for that.
9 Replies
I am not 100% sure this is what you are looking for...
If you are starting with data like...You can use the Pivot Column (Table.Pivot) feature to end up with...
Select the "Attribute" column and click the 'Pivot Column' icon on the transform ribbon.
Use the value column in the 'Values Column' and select 'Don't Aggregate' in the Advanced options.Hope this gets you pointed in the right direction.
Thank you! That is what I did to create the columns previously.Very useful feature. Now I'm just looking to consolidate the rows. Thanks for the tip!
The Group By feature would help with consolidation. Make sure that you explore the advanced options there as well. The ability to group and keep all related rows in a nested table is a very powerful tool.
Hi l9854,
Thank you for reaching out in Microsoft Community Forum.
I trust jgeddes response is accurate and will address your issue.
If you have any further questions or updates regarding your issue, feel free to ask, and we will look into that.If the Super User's answer meets your requirements, please consider marking it as the "Accept as Solution" and click on "yes"
Please continue using Microsoft community forum.
Regards,
Pavan.Hi l9854,
I wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.
If the community member's answer your query, please mark it as "Accept as Solution" and select "Yes" if it was helpful.
If you need any further assistance, feel free to reach out.
Thank you,
Pavan.Hi l9854,
I wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.
If the community member's answer your query, please mark it as "Accept as Solution" and select "Yes" if it was helpful.
If you need any further assistance, feel free to reach out.
Please continue using Microsoft community forum.
Thank you,
Pavan.
