Forum Discussion

H_insight's avatar
H_insight
Icon for Helper V rankHelper V
4 years ago
Solved

Can I achieve the below hierarchy?

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

Need Help
  • ImkeF's avatar
    ImkeF
    4 years ago

    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.

4 Replies

  • ImkeF's avatar
    ImkeF
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      H_insight
      Icon for Helper V rankHelper V
      4 years ago

      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's avatar
    ImkeF
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      H_insight
      Icon for Helper V rankHelper V
      4 years ago

      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.