Forum Discussion
Solved
Transpose and Group
Hi,
I'm struggling with a transposition/unpivoting problem.
My table looks like that :
Family
Product
F1
P1
F1
P2
F2
P3
...
Hello AilleryO ,
with a bit of tweaking the Group-code, you can achieve it like this:let Source = Table.FromRows( Json.Document( Binary.Decompress( Binary.FromText( "i45WcjNU0lEKMFSK1YGxjSBsIxDbGMI2AbFNkNimSOrNkNSbK8XGAgA=", BinaryEncoding.Base64 ), Compression.Deflate ) ), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Family = _t, Product = _t] ), #"Changed Type" = Table.TransformColumnTypes( Source, {{"Family", type text}, {"Product", type text}} ), #"Grouped Rows" = Table.Group(#"Changed Type", {"Family"}, {{"Product", each _[Product]}}), Custom1 = Table.FromColumns(#"Grouped Rows"[Product], #"Grouped Rows"[Family]) in Custom1
Hello AilleryO ,
with a bit of tweaking the Group-code, you can achieve it like this:
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"i45WcjNU0lEKMFSK1YGxjSBsIxDbGMI2AbFNkNimSOrNkNSbK8XGAgA=",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [Family = _t, Product = _t]
),
#"Changed Type" = Table.TransformColumnTypes(
Source,
{{"Family", type text}, {"Product", type text}}
),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Family"}, {{"Product", each _[Product]}}),
Custom1 = Table.FromColumns(#"Grouped Rows"[Product], #"Grouped Rows"[Family])
in
Custom1
ImkeF, I love this. Simple and elegant.
AilleryO Here's a method involving concatenating and transposing that may be more similar to your original idea:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjNU0lEKMFSK1YGxjSBsIxDbGMI2AbFNkNimSOrNkNSbK8XGAgA=",BinaryEncoding.Base64),Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Family = _t, Product = _t]),
#"Grouped Rows" = Table.Group(Source, {"Family"}, {{"Count", each Text.Combine([Product], "|")}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "Count", Splitter.SplitTextByDelimiter("|")),
#"Transposed Table" = Table.Transpose(#"Split Column by Delimiter")
in
#"Transposed Table"
