Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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], " ") ) )
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
89 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |