Columna combinar en Power Query

[Syndicate_Admin]

Mi tabla tiene alrededor de 500K filas con la siguiente estructura:

Quiero combinar la columna Dirección.

¿Alguien puede ayudar?

¡Gracias!

[Syndicate_Admin · Accepted solution]

@webportal,

Pruebe esto en Power Query. El paso Origen será su origen de datos. Utilicé un delimitador de comas en el paso GroupAddress; puede utilizar cualquier delimitador.

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

[Syndicate_Admin]

@smpa01

@DataInsights

¡Gracias, ambas soluciones funcionan!

[Syndicate_Admin · Accepted solution]

@DataInsights no tratando de pisar los dedos de los dedos de los en los dedos de los dos, pero en esta solución la unión se puede evitar y esto puede optimizar el rendimiento dramáticamente en un tbl grande.

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

[Syndicate_Admin]

@smpa01,

Me gusta tu solución. ¡Gracias por compartir! 🙂

[Syndicate_Admin · Accepted solution]

@webportal,

Pruebe esto en Power Query. El paso Origen será su origen de datos. Utilicé un delimitador de comas en el paso GroupAddress; puede utilizar cualquier delimitador.

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