Merge column in Power Query

[webportal]

My table has around 500K rows with the following structure:

I want to merge the Address column.

Can anyone help?

Thanks!

[DataInsights · Accepted solution]

@webportal,

Try this in Power Query. The Source step will be your data source. I used a comma delimiter in the GroupAddress step; you can use any delimiter.

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WMlTSUXJ0cgaShgYGSrE60UpApourG5CE8dw9PGE8IyDDy9sHpNoUrtrXzx9JdUBgEIxnDGQEh4QCSUtLS5h8WEQkkuqoqCgwLxYA",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Id = _t, Address = _t, Sales = _t]
  ),
  ChangeType = Table.TransformColumnTypes(
    Source,
    {{"Id", Int64.Type}, {"Address", type text}, {"Sales", Int64.Type}}
  ),
  FillDownId = Table.FillDown(ChangeType, {"Id"}),
  GroupAddress = Table.Group(
    FillDownId,
    {"Id"},
    {{"Combined Address", each Text.Combine([Address], ", "), type text}}
  ),
  TableAmount = Table.SelectRows(
    Table.SelectColumns(ChangeType, {"Id", "Sales"}),
    each ([Id] <> null)
  ),
  TableJoin = Table.NestedJoin(
    TableAmount,
    {"Id"},
    GroupAddress,
    {"Id"},
    "TableAddress",
    JoinKind.Inner
  ),
  ExpandTable = Table.ExpandTableColumn(
    TableJoin,
    "TableAddress",
    {"Combined Address"},
    {"Combined Address"}
  )
in
  ExpandTable

[webportal]

@smpa01 

@DataInsights 

Thank you, both solutions work!

[smpa01 · Accepted solution]

@DataInsights  not trying to step on your toes, but in this solution the Join can be avoided altogther and this can optimize performnace dramatically on a large tbl

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WMlTSUXJ0cgaShgYGSrE60UpApourG5CE8dw9PGE8IyDDy9sHpNoUrtrXzx9JdUBgEIxnDGQEh4QCSUtLS5h8WEQkkuqoqCgwLxYA",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Id = _t, Address = _t, Sales = _t]
  ),
  ChangeType = Table.TransformColumnTypes(
    Source,
    {{"Id", Int64.Type}, {"Address", type text}, {"Sales", Int64.Type}}
  ),
  FillDownId = Table.FillDown(ChangeType, {"Id"}),
  GroupAddress = Table.Group(
    FillDownId,
    {"Id"},
    {{"Combined Address", each Text.Combine([Address], ", "), type text},{"max", each List.Max([Sales]), type nullable number}}
  )
in
    GroupAddress

[DataInsights]

@smpa01,

I like your solution. Thanks for sharing! 🙂

[DataInsights · Accepted solution]

@webportal,

Try this in Power Query. The Source step will be your data source. I used a comma delimiter in the GroupAddress step; you can use any delimiter.

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WMlTSUXJ0cgaShgYGSrE60UpApourG5CE8dw9PGE8IyDDy9sHpNoUrtrXzx9JdUBgEIxnDGQEh4QCSUtLS5h8WEQkkuqoqCgwLxYA",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Id = _t, Address = _t, Sales = _t]
  ),
  ChangeType = Table.TransformColumnTypes(
    Source,
    {{"Id", Int64.Type}, {"Address", type text}, {"Sales", Int64.Type}}
  ),
  FillDownId = Table.FillDown(ChangeType, {"Id"}),
  GroupAddress = Table.Group(
    FillDownId,
    {"Id"},
    {{"Combined Address", each Text.Combine([Address], ", "), type text}}
  ),
  TableAmount = Table.SelectRows(
    Table.SelectColumns(ChangeType, {"Id", "Sales"}),
    each ([Id] <> null)
  ),
  TableJoin = Table.NestedJoin(
    TableAmount,
    {"Id"},
    GroupAddress,
    {"Id"},
    "TableAddress",
    JoinKind.Inner
  ),
  ExpandTable = Table.ExpandTableColumn(
    TableJoin,
    "TableAddress",
    {"Combined Address"},
    {"Combined Address"}
  )
in
  ExpandTable