Forum Discussion

space83's avatar
space83
Icon for Helper IV rankHelper IV
5 years ago
Solved

group and display all data in 1 row

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.

Need Help
Show and Tell
Tips and Tricks
  • Icey's avatar
    Icey
    5 years ago

    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's avatar
    space83
    5 years ago

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

     

3 Replies

  • Jimmy801's avatar
    Jimmy801
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    5 years ago

    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's avatar
    space83
    Icon for Helper IV rankHelper IV
    5 years ago

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