cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
space83
Helper IV
Helper IV

group and display all data in 1 row

Hi guys below is example of my dataset.

 

group_PN.jpg

how can I group the Part Number and at the same time the UOM data will show at another new column.

 

as example in data red crop above, the new UOM column will display ROLL, METER (in 1 line)

 

in msssql it can be done by using STUFF, but in DAX I dont know how.

2 ACCEPTED SOLUTIONS
Icey
Community Support
Community Support

Hi @space83 ,

 

Please let us know if what @Jimmy801 provides is what you want.

 

If it is, please always accept his reply as solution to your question so that people who may have the same question can get the solution directly.

 

If not, please let me know.

 

In addition, you can also create a calculated column or a measure like below:

Column = 
CONCATENATEX (
    FILTER (
        'Query1 (3)',
        'Query1 (3)'[Part Number] = EARLIER ( 'Query1 (3)'[Part Number] )
    ),
    [UOM],
    ", "
)

column.JPG

 

Measure = 
CONCATENATEX ( VALUES ( 'Query1 (3)'[UOM] ), [UOM], ", " )

measure.JPG

 

Best Regards,

Icey

View solution in original post

space83
Helper IV
Helper IV

Thanks everyone, finally I got what I want. The result I expect is below:

output.jpg

 

to achieve this, I just simply create a measure as below and thats it, done.

Measure = 
CONCATENATEX (
VALUES ( Append1[UOM] ),
Append1[UOM] ,
", "
)

 

View solution in original post

3 REPLIES 3
space83
Helper IV
Helper IV

Thanks everyone, finally I got what I want. The result I expect is below:

output.jpg

 

to achieve this, I just simply create a measure as below and thats it, done.

Measure = 
CONCATENATEX (
VALUES ( Append1[UOM] ),
Append1[UOM] ,
", "
)

 

View solution in original post

Icey
Community Support
Community Support

Hi @space83 ,

 

Please let us know if what @Jimmy801 provides is what you want.

 

If it is, please always accept his reply as solution to your question so that people who may have the same question can get the solution directly.

 

If not, please let me know.

 

In addition, you can also create a calculated column or a measure like below:

Column = 
CONCATENATEX (
    FILTER (
        'Query1 (3)',
        'Query1 (3)'[Part Number] = EARLIER ( 'Query1 (3)'[Part Number] )
    ),
    [UOM],
    ", "
)

column.JPG

 

Measure = 
CONCATENATEX ( VALUES ( 'Query1 (3)'[UOM] ), [UOM], ", " )

measure.JPG

 

Best Regards,

Icey

View solution in original post

Jimmy801
Community Champion
Community Champion

Hello @space83 

 

you can use Table.Group and use 1 function, that combines the column "UOM". Here a practicable example

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQrKz8lRitWBcHJTS1KLwDwjIK8ktbgEzDEGckzBLBMgKzEpGc5OSU0Ds02B7HSl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Part Number" = _t, UOM = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Part Number", Int64.Type}, {"UOM", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Part Number"}, {{"Uom combined", each Text.Combine(_[UOM], ", "), type text}})
in
    #"Grouped Rows"

 

Jimmy801_0-1608198459548.png

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

Top Kudoed Authors