Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
My table has around 500K rows with the following structure:
I want to merge the Address column.
Can anyone help?
Thanks!
Solved! Go to Solution.
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
Proud to be a Super User!
@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 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
I like your solution. Thanks for sharing! 🙂
Proud to be a Super User!
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
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |