how to merge multiple rows into one row with different column based on row values

[paulus66]

Hi All,
I am new into PowerBI and want to merge multiple rows into one row based on some values, searched lot but still cannot resolve my issues, any help will be greatly appreciated.

Eg.
Date Value
10/6/2016 318080
10/6/2016 300080
10/6/2016 298080
10/6/2016 288080
10/6/2016 278080
10/7/2016 328080
10/7/2016 318080


into

10/6/2016 318080 300080 298080 288080 278080
10/7/2016 328080 318080 NULL NULL NULL

Thanks

[v-yulgu-msft · Accepted solution]

Hi, @paulus66

You can achieve this result in edit query. Please paste the below Power Query formula into Advanced Editor:

let
    Source = Table.FromRecords({
        [date = "10/6/2016", value = 318080],
        [date = "10/6/2016", value = 300080],
        [date = "10/6/2016", value = 298080],
        [date = "10/6/2016", value = 288080],
        [date = "10/6/2016", value = 278080],
        [date = "10/7/2016", value = 328080],
        [date = "10/7/2016", value = 318080]
    }),
    AddRanking = (table, column, newColumn) =>
        Table.AddIndexColumn(Table.Sort(table, {{column, Order.Descending}}), newColumn, 1, 1),
    #"Grouped Rows" = Table.Group(Source, {"date"}, {{"Data", each _, type table}}),
    Transformed = Table.TransformColumns(#"Grouped Rows", {{"Data", each AddRanking(_, "value", "Rank")}}),
    #"Expand Data" = Table.ExpandTableColumn(Transformed, "Data", { "value", "Rank"}, { "value", "Rank"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expand Data", {{"Rank", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expand Data", {{"Rank", type text}}, "en-US")[Rank]), "Rank", "value")
in
    #"Pivoted Column"

Thanks,
Yuliana Gu

[KHorseman]

I haven't figured out how to do it in the query editor, but you can do it in DAX by hitting the New Table button:

NewTable = ADDCOLUMNS(
	SUMMARIZE(
		TableName,
		TableName[Date]
	),
	"Values",
	CALCULATE(
		CONCATENATEX(TableName, [Value], " ")
	)
)