Dynamic key columns in Power Query Table.Group

[rossid]

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.

[Chewdata · Accepted solution]

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), " & "))

[rossid]

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?

[rossid]

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