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.
Hello all,
I am struct with a problem in power query. Below is what data in single column.
Colum1
Name1
Value11
Value12
Value13
Name2
Value21
Value22
Value23
Result that I want is
Column 1, Column 2
Name1, "Value11,Value12,Value13"
Name2, "Value21,Value22,Value23"
Thanks to all for contribution to this community.
Solved! Go to Solution.
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"i45WUorViVbyS8xNNQSzwERYYk5pqiGGgBG6gDFCAGQCurwRuglGGCqAJsQCAA==",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type text) meta [Serialized.Text = true])
in
type table[Colum1 = _t]
),
#"Changed Type" = Table.TransformColumnTypes(Source, {{"Colum1", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Colum1] <> null and [Colum1] <> ""),
#"Grouped Rows" = Table.Group(
#"Filtered Rows",
{"Colum1"},
{{"all", each Table.Transpose(_)}},
GroupKind.Local,
(x, y) => Number.From(Text.Start(x[Colum1], 4) = Text.Start(y[Colum1], 4))
),
#"Expanded all" = Table.ExpandTableColumn(
#"Grouped Rows",
"all",
{"Column2", "Column3", "Column4"},
{"all.Column2", "all.Column3", "all.Column4"}
)
in
#"Expanded all"
this code works for any column that has groups of any size delimited by words that have the first 4 equal characters
let
Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("i65Wcs7PKc3NM1SyUvJLzE01VKrVQRIzNELlGqNyTVG4YAOMUA1QSja0RBNJMTJAE0k1AlobCwA=",BinaryEncoding.Base64),Compression.Deflate))),
acc = List.Accumulate(
Source[Column1]&{"Name"},{{}, {}},
(s,c)=>if s{0}={} then {{c},{}}
else if c is text and Text.StartsWith(c, "Name") then {{c}, s{1}&{{s{0}{0}}&{Text.Combine(List.Transform(List.Skip(s{0}),Text.From),", ")}}}
else {s{0}&{c}, s{1}}
){1},
result = Table.FromRows(acc)
in
result
If you have data below 2000 lines, you can also use the List.Accumulate function solution. Although I also like List.Accumulate, it handles more than 2000 lines of data and is prone to stack overflows
let
Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("i65Wcs7PKc3NM1SyUvJLzE01VKrVQRIzNELlGqNyTVG4YAOMUA1QSja0RBNJMTJAE0k1AlobCwA=",BinaryEncoding.Base64),Compression.Deflate))),
acc = List.Accumulate(
Source[Column1]&{"Name"},{{}, {}},
(s,c)=>if s{0}={} then {{c},{}}
else if c is text and Text.StartsWith(c, "Name") then {{c}, s{1}&{{s{0}{0}}&{Text.Combine(List.Transform(List.Skip(s{0}),Text.From),", ")}}}
else {s{0}&{c}, s{1}}
){1},
result = Table.FromRows(acc)
in
result
If you have data below 2000 lines, you can also use the List.Accumulate function solution. Although I also like List.Accumulate, it handles more than 2000 lines of data and is prone to stack overflows
Hi, @Greatbi1
let
Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("i65Wcs7PKc3NM1SyUvJLzE01VKrVQRIzNELlGqNyTVG4YAOMUA1QSja0RBNJMTJAE0k1AlobCwA=",BinaryEncoding.Base64),Compression.Deflate))),
result = Table.Group(Source,"Column1",{"Column2",each Text.Combine(List.Transform(List.Skip([Column1]),(x)=>Text.From(x)),", ")},0,(x,y)=> Byte.From(y is text and Text.StartsWith(y,"Name")) )
in
result
If my solution solves your problem, please mark it as a solution
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"i45WUorViVbyS8xNNQSzwERYYk5pqiGGgBG6gDFCAGQCurwRuglGGCqAJsQCAA==",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type text) meta [Serialized.Text = true])
in
type table[Colum1 = _t]
),
#"Changed Type" = Table.TransformColumnTypes(Source, {{"Colum1", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Colum1] <> null and [Colum1] <> ""),
#"Grouped Rows" = Table.Group(
#"Filtered Rows",
{"Colum1"},
{{"all", each Table.Transpose(_)}},
GroupKind.Local,
(x, y) => Number.From(Text.Start(x[Colum1], 4) = Text.Start(y[Colum1], 4))
),
#"Expanded all" = Table.ExpandTableColumn(
#"Grouped Rows",
"all",
{"Column2", "Column3", "Column4"},
{"all.Column2", "all.Column3", "all.Column4"}
)
in
#"Expanded all"
this code works for any column that has groups of any size delimited by words that have the first 4 equal characters
Hi @Anonymous , Just need one more help. Can you please explain how group rows is working.
I could not explain it better than as done by @imke here, where you can also find other examples of use of the fifth element.
do not hesitate to ask if you still need specific clarifications for "your" specific case ...
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.