Forum Discussion
Dynamic key columns in Power Query Table.Group
I have a requirement to concatenate two fields [Business Unit 1] and [Business Unit 2], and to make sure that the concatenated values are consistent (i.e. there should not be a 'Commercial & Operations' as well as an 'Operations & Commercial', these are effectively the same and should be named as such). I am using a method of Unpivot > Sort > Group/Combine to achieve this.
Source = tblApplications,
#"Unpivoted Only Selected Columns" = Table.Unpivot(Source, {"Business Unit 1", "Business Unit 2"}, "Attribute", "Business Unit"),
#"Removed Columns2" = Table.RemoveColumns(#"Unpivoted Only Selected Columns",{"Attribute"}),
#"Sorted Rows" = Table.Sort(#"Removed Columns2",{{"Business Unit", Order.Ascending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"AppID"}, {{"Business Units", each Text.Combine(List.Sort([Business Unit])," & "), type text}})I want all the columns in the original table retained, but also I want to future proof this for any additional columns being added. Above in the Table.Group you only see "AppID", but ideally this would be every column but the column being aggregated. I have read that there is not an 'all columns' option, therefore all the key columns need to be explicit, but I foresee issues with this as new columns added to the source will not be explicit and therefore will not appear in the query. Any ideas? Perhaps I'm going about this in the wrong way so welcome any pointers.
Thanks for providing the data.
in the supplied data, the empty columns were filled with a space " ", so I first replaced the value " " with null
Please try to add the following custom Column:
= Table.AddColumn(#"Replaced Value", "Concatenated", each Text.Combine(List.Sort({[Business Unit 1], [Business Unit 2]}, Order.Descending), " & "))
4 Replies
- Chewdata
Responsive Resident
Thanks for providing the data.
in the supplied data, the empty columns were filled with a space " ", so I first replaced the value " " with null
Please try to add the following custom Column:
= Table.AddColumn(#"Replaced Value", "Concatenated", each Text.Combine(List.Sort({[Business Unit 1], [Business Unit 2]}, Order.Descending), " & "))Thanks so much. Such an obvious and simple answer
- Chewdata
Can you supply us with some sample data and a example of the desired result?
As you'll see by the two final columns, values can be entered either way round. In the case of APP10 and APP11 I would like them to both have a column with value Commercial & Operations.
AppID Application Name Vendor Lifecycle Status Documentation Support Model Quality of Support Vendor Size Business Unit 1 Business Unit 2 APP001 Application 1 Vendor 1 Active High Vendor Moderate Large Enterprise Commercial APP002 Application 2 Vendor 2 Active Low Vendor Low Sub-100 Employees Commercial APP003 Application 3 Vendor 3 Active High Vendor Moderate Sub-1000 Employees Commercial APP004 Application 4 Vendor 4 Active Moderate Vendor High Sub-10 Employees Commercial APP005 Application 5 Vendor 5 Active High Vendor High Sub-1000 Employees Commercial APP006 Application 6 Vendor 6 Planned High Vendor High Large Enterprise Commercial APP007 Application 7 Vendor 7 Active High Vendor High Sub-10 Employees Commercial APP008 Application 8 Vendor 8 Active High IT High Sub-10 Employees Operations Commercial APP009 Application 9 Vendor 9 Active Low Vendor High Sub-10 Employees Commercial APP010 Application 10 Vendor 10 Active High Vendor High Sub-1000 Employees Operations Commercial APP011 Application 11 Vendor 11 End of Life High IT High Sub-1000 Employees Commercial Operations APP012 Application 12 Vendor 12 Active Moderate IT High Large Enterprise Operations
