If selected level of category is lower than configured level then show lowest level

Question

I have following table

Category

Category Id     |    Name     | Parent Id   |   Category Level
-------------------------------------------------------------
     1          |   Vehicle   |    Null     |         1
     2          |     Car     |     1       |         2
     3          |    Sedan    |     2       |         3
     4          |   Computer  |    Null     |         1
     5          |    Laptop   |     4       |         2
     6          |    Gaming   |     5       |         3
     7          |   Alienware |     6       |         4

Level of category is decided based on Category hierarchy.

Here in Category table we have 2 main categories Vehicle and Computer

If I have selected the Level 4 then I want records on Category Level 4 if there is no level 4 in that category then I want the records on the lowest level. So in the output we'll get Sedan(Lowest in Vehicle) and Alienware(Level 4).

Can anyone suggest how can I achieve this in Power BI.

Answer 1

Considering Highest level: 4, let your table Category contains records as below

Now create a new table Category_new in Power Query Editor using this below code-

let
    Source = Category,
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category Id", Int64.Type}, {"Name", type text}, {"Parent Id", Int64.Type}, {"Category Level", Int64.Type}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"Category Level", "Category Id", "Name", "Parent Id"}),
    #"Merged Queries" = Table.NestedJoin(#"Reordered Columns", {"Parent Id"}, #"Reordered Columns", {"Category Id"}, "Reordered Columns", JoinKind.LeftOuter),
    #"Expanded Reordered Columns" = Table.ExpandTableColumn(#"Merged Queries", "Reordered Columns", {"Parent Id"}, {"Reordered Columns.Parent Id"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Reordered Columns",{{"Reordered Columns.Parent Id", "Parent Id 2"}}),
    #"Merged Queries1" = Table.NestedJoin(#"Renamed Columns", {"Parent Id 2"}, #"Renamed Columns", {"Category Id"}, "Renamed Columns", JoinKind.LeftOuter),
    #"Expanded Renamed Columns" = Table.ExpandTableColumn(#"Merged Queries1", "Renamed Columns", {"Parent Id"}, {"Renamed Columns.Parent Id"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Expanded Renamed Columns",{{"Renamed Columns.Parent Id", "Parent Id 3"}}),
    #"Merged Queries2" = Table.NestedJoin(#"Renamed Columns1", {"Parent Id 3"}, #"Renamed Columns1", {"Category Id"}, "Renamed Columns1", JoinKind.LeftOuter),
    #"Expanded Renamed Columns1" = Table.ExpandTableColumn(#"Merged Queries2", "Renamed Columns1", {"Parent Id"}, {"Renamed Columns1.Parent Id"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Expanded Renamed Columns1",{{"Renamed Columns1.Parent Id", "Parent Id 4"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns2", "Custom", each List.Min({[Category Id], [Parent Id], [Parent Id 2], [Parent Id 3], [Parent Id 4]})),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each List.Max({[Category Id], [Parent Id], [Parent Id 2], [Parent Id 3], [Parent Id 4]})),
    #"Renamed Columns3" = Table.RenameColumns(#"Added Custom1",{{"Custom", "Root Category"}, {"Custom.1", "Max Level"}}),
    #"Merged Queries3" = Table.NestedJoin(#"Renamed Columns3", {"Root Category"}, #"Renamed Columns3", {"Category Id"}, "Renamed Columns3", JoinKind.LeftOuter),
    #"Expanded Renamed Columns3" = Table.ExpandTableColumn(#"Merged Queries3", "Renamed Columns3", {"Name"}, {"Renamed Columns3.Name"}),
    #"Renamed Columns4" = Table.RenameColumns(#"Expanded Renamed Columns3",{{"Renamed Columns3.Name", "Root Category Name"}}),
    #"Grouped Rows" = Table.Group(#"Renamed Columns4", {"Root Category Name"}, {{"max_level", each List.Max([Max Level]), type nullable number}}),
    #"Merged Queries4" = Table.NestedJoin(#"Grouped Rows", {"max_level"}, Category, {"Category Id"}, "Category", JoinKind.LeftOuter),
    #"Expanded Category" = Table.ExpandTableColumn(#"Merged Queries4", "Category", {"Name"}, {"Category.Name"}),
    #"Renamed Columns5" = Table.RenameColumns(#"Expanded Category",{{"Category.Name", "lowest in category"}})
in
    #"Renamed Columns5"

Here is your final output-