Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
rossid
Frequent Visitor

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.

1 ACCEPTED SOLUTION
Chewdata
Super User
Super User

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

View solution in original post

4 REPLIES 4
Chewdata
Super User
Super User

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
Super User
Super User

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.

 

rossid_0-1723467858152.png

AppIDApplication NameVendorLifecycle StatusDocumentationSupport ModelQuality of SupportVendor SizeBusiness Unit 1Business Unit 2
APP001Application 1Vendor 1ActiveHighVendorModerateLarge EnterpriseCommercial 
APP002Application 2Vendor 2ActiveLowVendorLowSub-100 EmployeesCommercial 
APP003Application 3Vendor 3ActiveHighVendorModerateSub-1000 EmployeesCommercial 
APP004Application 4Vendor 4ActiveModerateVendorHighSub-10 EmployeesCommercial 
APP005Application 5Vendor 5ActiveHighVendorHighSub-1000 EmployeesCommercial 
APP006Application 6Vendor 6PlannedHighVendorHighLarge EnterpriseCommercial 
APP007Application 7Vendor 7ActiveHighVendorHighSub-10 EmployeesCommercial 
APP008Application 8Vendor 8ActiveHighITHighSub-10 EmployeesOperationsCommercial
APP009Application 9Vendor 9ActiveLowVendorHighSub-10 EmployeesCommercial 
APP010Application 10Vendor 10ActiveHighVendorHighSub-1000 EmployeesOperationsCommercial
APP011Application 11Vendor 11End of LifeHighITHighSub-1000 EmployeesCommercialOperations
APP012Application 12Vendor 12ActiveModerateITHighLarge EnterpriseOperations 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.