Column to Row

[AllanBerces]

Hi good day, is it possible in PQ or Summurized table to combine the value in my column into row something like this

OUTPUT

Location	Task
shfh-y0998	12345trtr
gfhdgh-354y	12345trtr
45-dgfd	565656kjhjhkh
fdhdh098-5	565656kjhjhkh
hdfhtrh-453	565656kjhjhkh
yuryt7865	tryer6666
tjrtyuyjjfyu	tryer6666
hdgf	tryer6666

[Kedar_Pande · Accepted solution]

@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

[SamInogic · Accepted solution]

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

Task	Location
12345trtr	shfh-y0998, gfhdgh-354y
565656kjhjhkh	45-dgfd, fdhdh098-5, hdfhtrh-453
tryer6666	yuryt7865, tjrtyuyjjfyu, hdgf

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!

[SamInogic · Accepted solution]

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

Task	Location
12345trtr	shfh-y0998, gfhdgh-354y
565656kjhjhkh	45-dgfd, fdhdh098-5, hdfhtrh-453
tryer6666	yuryt7865, tjrtyuyjjfyu, hdgf

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!

[AllanBerces]

Hi @SamInogic @Kedar_Pande thank you, i got it.

[AllanBerces]

Hi @Kedar_Pande @SamInogic sorry but Im frezz on this

let
Source = #"115MRR",
#"Merged Columns" = Table.CombineColumns(Source,{"ACCOUNT NO", "MATERIAL CODE"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"WON-ARTICLE No."),
#"Removed Other Columns" = Table.SelectColumns(#"Merged Columns",{"WON-ARTICLE No.", "LOCATION"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"WON-ARTICLE No.", "TASK"}}),
#"Grouped Rows" = Table.Group(#"Renamed Columns", {"TASK"}, {{"Count", each _, type table [TASK=text, LOCATION=nullable text]}})
in
#"Grouped Rows"

[Future_To_BI · Accepted solution]

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.

[Kedar_Pande · Accepted solution]

@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

[AllanBerces]

Hi @Kedar_Pande sorry but i cannot get, can help on the sample