Merge rows of table

[AlessandroBet]

Hi,

i have a table with 2 columns

Column A: list of many email with some duplicate

Column B: with some text

I need to group all the identical email of column A in 1 row merging all the text of column B separating them with ";" and excluding the double text

[Anonymous]

Hi @AlessandroBet ,

You could duplicate the original table and only keep the necessary column , the paste the following M in Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wqqio0ANih/TcxMwcveT8XCUdpZDUihJdR6VYHXzS1mDKCb8qZ7B0ZWWlHhBjlY4FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type text}, {"B", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"B", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "B"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"B", type text}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type1"),
    #"Grouped Rows" = Table.Group(#"Removed Duplicates", {"A"}, {{"Count", each Text.Combine([B],";"),type text}})
   
in
    #"Grouped Rows"

Output:

Then could merge the two tables to relate the combined column to the original table.

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.

[AlessandroBet]

Hi now it works.

The problem was because i have "; " and in your text ";". 

But the test done is only with 2 columns and i have to add others.

Can you help me?

[AlessandroBet]

thanks but the result is not what i have expected after merging the 2 tables

[AlessandroBet]

Many thanks and both the solution work not properly with same errors 

I have changed the table like the image below

and the result is the following

[vikrambasriyar]

@AlessandroBet when you are loading the table use split in to row for column B and then use dax too create the calculated table,in SUMMARIZE you can use whatever column you want.

[DataInsights]

@AlessandroBet,

The reason is that the row you added has a different value in Column C for xxx.xxx@gmail.com (this email address has two values in Column C). Does your actual table have only two columns? If so, then the first script I provided should work (it provides the desired result in your original post).

[AlessandroBet]

Here the code

let
Source = #"Webinar+Contacts",
#"Filtered Rows" = Table.SelectRows(Source, each ([#"Company Status (Account) (Account)"] <> "Suspect")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Email Address", "Software Interests", "Account", "(Do Not Modify) Contact ID", "(Do Not Modify) Row Checksum", "(Do Not Modify) Modified On", "Integration Key"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Other Columns",{"(Do Not Modify) Contact ID", "(Do Not Modify) Row Checksum", "(Do Not Modify) Modified On", "Account", "Email Address", "Software Interests", "Integration Key"}),

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Email Address", type text}, {"Software Interests", type text}}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Software Interests", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Software Interests"),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Software Interests", type text}})

in
#"Reordered Columns"

and the result is the followiing

 As you can see the column "Accounts" is not groupped as i would like. I have inserted the numbers to identify the indentical text for columns "Accounts" and " Email Address". EX: all the number 4 should be in 1 row and joint the text of "Software Interes" but removing the duplicate.

Result for column "Software Interest" = "PowerPack for Revit, Archivision, Infreworks 360, Dynamo, AutoCAD Civil 3D, Opentree"

[DataInsights]

@AlessandroBet,

Try this. The steps prior to "SplitColumn" will be your actual source. You can expand this script to include the additional columns in your table.

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "jcwxD8IgEAXgv3Jh7mCBuHQixcHJxpV0QKSRtOUMRRr/vdrIZEjcLu9975QiNanIYdZu+hwdrjZ02owwYICzTS6+U12TvlKEZkmL8kI3ybJkRWnYJnmWvCiv/FeKYG7JLQ59Afz/6uiHoFcM4wJsv2tAPr2esQHxiNgKCa1LbgImC/PT3foYrP3W/Qs=",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [
        Account = _t,
        #"Email Address" = _t,
        #"Software Interests" = _t,
        #"Integration Key" = _t
      ]
  ),
  ChangeType = Table.TransformColumnTypes(
    Source,
    {{"Account", type text}, {"Email Address", type text}, {"Software Interests", type text}}
  ),
  SplitColumn = Table.ExpandListColumn(
    Table.TransformColumns(
      ChangeType,
      {
        {
          "Software Interests",
          Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv),
          let
            itemType = (type nullable text) meta [Serialized.Text = true]
          in
            type {itemType}
        }
      }
    ),
    "Software Interests"
  ),
  ChangeType2 = Table.TransformColumnTypes(SplitColumn, {{"Software Interests", type text}}),
  TrimText = Table.TransformColumns(ChangeType2, {{"Software Interests", Text.Trim, type text}}),
  RemoveDuplicates = Table.Distinct(TrimText),
  GroupRows = Table.Group(
    RemoveDuplicates,
    {"Account"},
    {
      {"Software Interests", each Text.Combine([Software Interests], "; "), type text},
      {
        "All",
        each _,
        type table [
          Account = nullable text,
          Email Address = text,
          Software Interests = nullable text,
          Integration Key = text
        ]
      }
    }
  ),
  ExpandAll = Table.ExpandTableColumn(
    GroupRows,
    "All",
    {"Email Address", "Integration Key"},
    {"Email Address", "Integration Key"}
  ),
  RemoveDuplicates2 = Table.Distinct(ExpandAll)
in
  RemoveDuplicates2

[AlessandroBet]

I have repalced your sourse with my

let
Source = #"Webinar+Contacts",
ChangeType = Table.TransformColumnTypes(
Source,
{{"Account", type text}, {"Email Address", type text}, {"Software Interests", type text}}
),
SplitColumn = Table.ExpandListColumn(
Table.TransformColumns(
ChangeType,
{
{
"Software Interests",
Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv),
let
itemType = (type nullable text) meta [Serialized.Text = true]
in
type {itemType}
}
}
),
"Software Interests"
),
ChangeType2 = Table.TransformColumnTypes(SplitColumn, {{"Software Interests", type text}}),
TrimText = Table.TransformColumns(ChangeType2, {{"Software Interests", Text.Trim, type text}}),
RemoveDuplicates = Table.Distinct(TrimText),
GroupRows = Table.Group(
RemoveDuplicates,
{"Account"},
{
{"Software Interests", each Text.Combine([Software Interests], "; "), type text},
{
"All",
each _,
type table [
Account = nullable text,
Email Address = text,
Software Interests = nullable text,
Integration Key = text
]
}
}
),
ExpandAll = Table.ExpandTableColumn(
GroupRows,
"All",
{"Email Address", "Integration Key"},
{"Email Address", "Integration Key"}
),
RemoveDuplicates2 = Table.Distinct(ExpandAll)
in
RemoveDuplicates2

But the result is not what i have expeted

[DataInsights]

@AlessandroBet,

Try this in Power Query:

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45Wqqio0ANih/TcxMwcveT8XCUdpZDUihJdR6VYHXzS1gpg2gm/MmewdGVlpR4QY5WOBQA=",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [#"Column A" = _t, #"Column B" = _t]
  ),
  ChangeType = Table.TransformColumnTypes(
    Source,
    {{"Column A", type text}, {"Column B", type text}}
  ),
  SplitColumn = Table.ExpandListColumn(
    Table.TransformColumns(
      ChangeType,
      {
        {
          "Column B",
          Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv),
          let
            itemType = (type nullable text) meta [Serialized.Text = true]
          in
            type {itemType}
        }
      }
    ),
    "Column B"
  ),
  ChangeType2 = Table.TransformColumnTypes(SplitColumn, {{"Column B", type text}}),
  TrimText = Table.TransformColumns(ChangeType2, {{"Column B", Text.Trim, type text}}),
  RemoveDuplicates = Table.Distinct(TrimText),
  GroupRows = Table.Group(
    RemoveDuplicates,
    {"Column A"},
    {{"Column B", each Text.Combine([Column B], "; "), type text}}
  )
in
  GroupRows

[vikrambasriyar]

Step 1: Create the table in Power Query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wqqio0ANih/TcxMwcveT8XCUdpZDUihJdR6VYHXzS1mDKCb8qZ7B0ZWWlHhBjlY4FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type text}, {"B", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"B", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "B"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"B", type text}})
in
    #"Changed Type1"

 Step 2: Create a calculated table in DAX

Req_Table = 
SUMMARIZE(Test,Test[A],"RecCol",CALCULATE(CONCATENATEX(VALUES(Test[B]),Test[B],";",Test[B],DESC),ALLEXCEPT(Test,Test[A])))

[AlessandroBet]

Hi many thanks it works correctly but i'm not expert of these script so i would like now to use it for the correct columns into my table.

On the result i see still the email xxx.xxx etc. and i shoold replace with those in the correct column

[DataInsights]

@AlessandroBet,

I modified the script to include additional columns. Where you see references to Column C and Column D, insert your column names and follow the pattern.

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45Wqqio0ANih/TcxMwcveT8XCUdpZDUihJdRyDDLzE31RBI++QnJ5Zk5ucZKsXq4NNirQCmnUjX6oxTS2VlpR4Q49ZihKTFSCk2FgA=",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [#"Column A" = _t, #"Column B" = _t, #"Column C" = _t, #"Column D" = _t]
  ),
  ChangeType = Table.TransformColumnTypes(
    Source,
    {{"Column A", type text}, {"Column B", type text}}
  ),
  SplitColumn = Table.ExpandListColumn(
    Table.TransformColumns(
      ChangeType,
      {
        {
          "Column B",
          Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv),
          let
            itemType = (type nullable text) meta [Serialized.Text = true]
          in
            type {itemType}
        }
      }
    ),
    "Column B"
  ),
  ChangeType2 = Table.TransformColumnTypes(SplitColumn, {{"Column B", type text}}),
  TrimText = Table.TransformColumns(ChangeType2, {{"Column B", Text.Trim, type text}}),
  RemoveDuplicates = Table.Distinct(TrimText),
  GroupRows = Table.Group(
    RemoveDuplicates,
    {"Column A"},
    {
      {"Column B", each Text.Combine([Column B], "; "), type text},
      {
        "All",
        each _,
        type table [
          Column A = nullable text,
          Column B = text,
          Column C = nullable text,
          Column D = nullable text
        ]
      }
    }
  ),
  ExpandAll = Table.ExpandTableColumn(
    GroupRows,
    "All",
    {"Column C", "Column D"},
    {"Column C", "Column D"}
  ),
  RemoveDuplicates2 = Table.Distinct(ExpandAll)
in
  RemoveDuplicates2