group and display all data in 1 row

[space83]

Hi guys below is example of my dataset.

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.

[Icey · Accepted solution]

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

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

Best Regards,

Icey

[space83 · Accepted solution]

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

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

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

[Icey · Accepted solution]

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

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

Best Regards,

Icey

[Jimmy801]

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"

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