return mutiple rows into one row in a visual

[Anonymous]

Hi,

I have this tablke

Transaction	item price	price
1	apple	2
1	strawberry	4
2	apple	2

I want to return a visual with the following info

Transaction	detail
1	apple 2; straberry 4
2	apple 2

Is this possible?

thanks!

[v-zhenbw-msft]

Hi @Anonymous ,

You can do the following steps in Power Query Editor.

1. Merge [item price] column and [price] column.

2. Then we need to Group the [Transaction] column.

3. We add a custom column to get the result.

At last we can delete the [Group] column.

The complete M query as follows.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUosKMhJBdJGSrE6EJHikqLE8qTUoqJKIMcELGyEqjAWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Transaction = _t, #"item price" = _t, price = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Transaction", Int64.Type}, {"item price", type text}, {"price", Int64.Type}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"price", type text}}, "en-US"),{"item price", "price"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"D"),
    #"Grouped Rows" = Table.Group(#"Merged Columns", {"Transaction"}, {{"Group", each _, type table [Transaction=nullable number, D=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Text.Combine([Group][D],",")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Group"})
in
    #"Removed Columns"

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.

[Anonymous]

@v-zhenbw-msft 

Thanks for taking the time to have a look, and you certainly taught me something that will no doubt be useful later, but I needed to do the query in DAX as some of the columns I'm referencing are calculated columns from DAX. 

[amitchandak]

@Anonymous , In case you need aggregation for price

measure =
var _tab = summarize(Table, table[Transaction], Table[item], "_1", sum(Table[price])) //may be max
return
concatenatex(_tab, EE & " " & _1 ,";")

Otherwise @Pragati11 solution will work

[Pragati11 · Accepted solution]

Hi @Anonymous ,

Try creating a measure as follows:

Clubbed value new = CONCATENATEX('Tablename', 'Tablename'[Transaction Item] & " " & 'Tablename'[Price], ", ")

Replace Tablename in above expression with your table name and also check if I have used the correct column names.

Thanks,

Pragati

[Anonymous]

@Pragati11 

Perfect, thanks!