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.
Hi,
I'm struggling with a transposition/unpivoting problem.
My table looks like that :
Family |
Product |
F1 |
P1 |
F1 |
P2 |
F2 |
P3 |
F4 |
P4 |
F4 |
P5 |
F1 |
P6 |
F2 |
P7 |
and my required outcome would be :
F1 |
F2 |
F4 |
P1 |
P3 |
P4 |
P2 |
P7 |
P5 |
P6 |
P3 |
|
I managed to get all product values with a concatenate, but cannot find a way to have them in columns.
Any guess ?
Thanks for your help.
Solved! Go to Solution.
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
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Thanks a lot that is beautiful 🙂
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
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
@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"
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.