Can I achieve the below hierarchy?

[H_insight]

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

[ImkeF · Accepted 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.

[H_insight]

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.

[ImkeF]

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.

[H_insight]

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.