Forum Discussion
Transform a table for analysis
hi everyone, i need some help doing this transformation:
Need to convert this:
MTM SBB
1 A
2 B
1 C
3 D
2 A
into this:
MTM SBB1 SBB2 SBB3
1 A C
2 B A
3 D
Where each SBB is extracted from the first table. In the real case i can have 7 SBBs at maximum.
Was thinking about giving any SBB a Rank based on the appearences in each MTM and then with a vlookup fill the columns:
MTM SBB RANK
1 A 1
2 B 1
1 C 2
3 D 1
2 A 2
, but couldnt use the rank function appropietly or even if there is any other approach would be really helpful.
Thanks in advance!
2 Replies
julianst24 Attached file has two options, use the one that works best for you.
✨ Follow us on LinkedIn
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Hi,
This M code works
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], Partition = Table.Group(Source, {"MTM"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}), #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"SBB", "Index"}, {"SBB", "Index"}), #"Added Prefix" = Table.TransformColumns(#"Expanded Partition", {{"Index", each "MTM" & Text.From(_, "en-IN"), type text}}), #"Pivoted Column" = Table.Pivot(#"Added Prefix", List.Distinct(#"Added Prefix"[Index]), "Index", "SBB") in #"Pivoted Column"Hope this helps.
