Combine Rows

[Anonymous]

Hi - I am trying to combine rows for "PVColumnName" for each ProcessOrderNbr. I would like each PrcocessOrderNbr to have "Fail" and the failed comment in the same column. I tried the method where you group by table and I am getting an error when I try and do it that way. Any Ideas?

I want my table to look like this but with the Fail and Failed coment combined. 

Thank you in Advanced! - Rachel 

[v-eqin-msft · Accepted solution]

Hi @Anonymous ,

According to my understanding, you want to combine values of each category, right?

I did it in two ways, you could take a look at the pbix file here.

1. Use the following formula to creata a new table:

Table 2 =
VAR _t =
    SUMMARIZECOLUMNS ( 'Table'[ProcessOrderNbr], 'Table'[PVColumnName] )
VAR _t1 =
    DISTINCT (
        SELECTCOLUMNS (
            ADDCOLUMNS (
                _t,
                "a",
                    CONCATENATEX (
                        FILTER (
                            _t,
                            [PVColumnName] <> "Fail"
                                && [ProcessOrderNbr] = EARLIER ( [ProcessOrderNbr] )
                        ),
                        [PVColumnName],
                        ""
                    )
            ),
            "Pro", [ProcessOrderNbr],
            "PVC", [a]
        )
    )
VAR _t2 =
    DISTINCT ( SELECTCOLUMNS ( _t, "Pro", [ProcessOrderNbr], "PVC", "Fail" ) )
RETURN
    UNION ( _t1, _t2 )

2. In Power Query:

•  New Table1: Duplicate the original table-->Select columns-->Filter PVC="Fail"-->Remove duplicate:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjYwNrMwM1bSUTIAARdTJ4sQA1Mg1y0xM0cpVod4FaaYKkL1jc0cdYCkhaOClYJTqJOTjyslOiwNYDpMjD0sTAyMMFxBhAozTBWhugYWjjoKobqGBkDLdQ0NHQmpNzQGKzQBk6aOeiDHOroo+DhGuQYp+DoGeSvFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, ProcessOrderNbr = _t, PVColumnName = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"ProcessOrderNbr", type text}, {"PVColumnName", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"ProcessOrderNbr", "PVColumnName"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([PVColumnName] = "Fail")),
    #"Removed Duplicates" = Table.Distinct(#"Filtered Rows")
in
    #"Removed Duplicates"

• New Table2: Duplicate the original table-->Select columns-->Filter PVC<>"Fail"-->Then as @Ashish_Mathur  suggested, use Text.Combine() to group rows. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjYwNrMwM1bSUTIAARdTJ4sQA1Mg1y0xM0cpVod4FaaYKkL1jc0cdYCkhaOClYJTqJOTjyslOiwNYDpMjD0sTAyMMFxBhAozTBWhugYWjjoKobqGBkDLdQ0NHQmpNzQGKzQBk6aOeiDHOroo+DhGuQYp+DoGeSvFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, ProcessOrderNbr = _t, PVColumnName = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"ProcessOrderNbr", type text}, {"PVColumnName", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"ProcessOrderNbr", "PVColumnName"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each [PVColumnName] <> "Fail"),
    #"Grouped Rows" = Table.Group( #"Filtered Rows", {"ProcessOrderNbr"}, {{"PVColumnName", each Text.Combine(List.Distinct([PVColumnName]), ", "), type text}})
in
    #"Grouped Rows"

• At last, append these two new tables.

The final output is shown below:

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

[charliej31]

I want to create a new column with row 1 value "Glasgow" which combines the values of Glasgow North and Glasgow South and renames row one as Glasgow. In row 1, I get an error when adding the values together because it is expecting a number, not a text. Is there a way to add the numerical values and rename the text fields?

[Ashish_Mathur]

Hi,

Do you want to see only 2 rows - one for each ProcessOrderNbr?  Share the link from where i can download your PBI file.

[Anonymous]

No I only want to see one row for each process number. The row should have Fail and the comment in the same cell. I can't share the file because it is confidential information. 

[Ashish_Mathur]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Process OrderNbr"}, {{"All names", each Text.Combine(List.Distinct([PVColumnName]), ", "), type text}}),
    Joined = Table.Join(Source, "Process OrderNbr", #"Grouped Rows", "Process OrderNbr"),
    #"Removed Columns" = Table.RemoveColumns(Joined,{"PVColumnName"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns")
in
    #"Removed Duplicates"

Hope this helps.