Column to Row

AllanBerces 

Yes, do this in Power Query.

Select the Task column, then Group By.
Group by Task, create a new column as "All Rows" operation.
Add a custom column: Text.Combine([AllRows][Location], ", ")
Remove the AllRows column.

If this answer helped, please click 👍 or Accept as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pande

Hi,

Yes, this is possible in Power Query using Group By + Text.Combine.

Steps in Power Query

1. Open Power Query Editor
2. Select the Task column
3. Go to Transform → Group By
4. Group by Task
5. Add a custom aggregation for Location

Use this M code example:

Table.Group(

    Source,

    {"Task"},

    {

        {"Location", each Text.Combine([Location], ", "), type text}

    }

)

Expected Output

This will combine all Location values into a single row separated by commas based on the same Task.

You can also do this in DAX using CONCATENATEX(), but Power Query is the better approach if you want a summarized table and improved performance.

Hope this helps!

Thanks!

Hi AllanBerces
You're already at the Grouped Rows step. The only thing missing is converting the nested table into a comma-separated list of locations.

Replace:

#"Grouped Rows" = Table.Group(#"Renamed Columns", {"TASK"}, {{"Count", each _, type table [TASK=text, LOCATION=nullable text]}})

with:

#"Grouped Rows" =
Table.Group(
#"Renamed Columns",
{"TASK"},
{
{
"LOCATION",
each Text.Combine(
List.Transform([LOCATION], Text.From),
", "
),
type text
}
}
)

Then keep:

in
#"Grouped Rows"

This will give you one row per TASK and combine all LOCATION values into a single field separated by commas.