Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi All,
I am trying to achieve the below hierarchy in PQ, but not getting much success. Any chance of help?
Expected outcome:
Sample excel file Link.
Many thanks
Solved! Go to Solution.
Hi @H_insight ,
this can be done with a bit of Power Query Magic:
// Result
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Client Key", type text}, {"Client", type text}, {"Type", type text}, {"Score", Int64.Type}, {"Status", type text}, {"ref Key", type text}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Type", "Type - Copy"),
#"Added Index" = Table.AddIndexColumn(#"Duplicated Column", "Index", 0, 1, Int64.Type),
#"Pivoted Column" = Table.Pivot(#"Added Index", List.Distinct(#"Added Index"[Type]), "Type", "Type - Copy"),
#"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"Client Key", "Client"} & SortOrder & {"Score", "Status", "ref Key"}),
#"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Client Key", Order.Ascending}} & List.Transform(SortOrder, each {_, Order.Descending})),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
#"Removed Columns"
// SortOrder
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
Custom1 = #"Changed Type"[Column1]
in
Custom1
Please check the file enclosed.
Please not that I've used a "helper table" for the sort order that you have had in your file already.
If there will be more actions or projects in your data, you can adjust that table and the result query will consider these values accordingly.
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
Hi @H_insight ,
this can be done with a bit of Power Query Magic:
// Result
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Client Key", type text}, {"Client", type text}, {"Type", type text}, {"Score", Int64.Type}, {"Status", type text}, {"ref Key", type text}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Type", "Type - Copy"),
#"Added Index" = Table.AddIndexColumn(#"Duplicated Column", "Index", 0, 1, Int64.Type),
#"Pivoted Column" = Table.Pivot(#"Added Index", List.Distinct(#"Added Index"[Type]), "Type", "Type - Copy"),
#"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"Client Key", "Client"} & SortOrder & {"Score", "Status", "ref Key"}),
#"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Client Key", Order.Ascending}} & List.Transform(SortOrder, each {_, Order.Descending})),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
#"Removed Columns"
// SortOrder
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
Custom1 = #"Changed Type"[Column1]
in
Custom1
Please check the file enclosed.
Please not that I've used a "helper table" for the sort order that you have had in your file already.
If there will be more actions or projects in your data, you can adjust that table and the result query will consider these values accordingly.
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
Many thanks @ImkeF for your help. Who would say no to MAGIC 🪄🔮? 🙂
Nice sorting trick using a secondary table! I learnt something new today.
Have a good weekend.
Hi @H_insight ,
if this request is still valid, I would recommend to describe the desired logic a bit more and update the link to the file.
That would raise the chance for anyone picking this up here.
Otherwise please close this thread.
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
Hi @ImkeF
I have updated the file link. The sample file contains a table that holds customer scores based on their "Status" & "Type". My goal is to have a holistic view per customer in a specific structure and order.
Based on the screenshot below, I want to create a custom hierarchy that starts with "Client" -> "Type" transposed (Project 1, Action 1, Action 2, Action 3 and Action 4) -> Score -> Status -> Ref Key).
Many thanks for looking into my question.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
15 | |
13 | |
13 | |
13 | |
12 |