sql - 如果选择的类别级别低于配置的级别，则显示最低级别

Question

我有下表

类别

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

类别的级别是根据类别层次结构确定的。

在类别表中，我们有 2 个主要类别车辆和计算机

如果我选择了第 4 级，那么我想要第 4 级的记录，如果该类别中没有第 4 级，那么我想要最低级别的记录。所以在输出中，我们将得到轿车（车辆中最低）和 Alienware（4 级）。

谁能建议我如何在 Power BI 中实现这一点。

标签： sqlsql-serverpowerbipowerbi-desktoppowerbi-datasource

Answer 1

考虑最高级别：4，让您的表类别包含如下记录

现在使用下面的代码在Power Query 编辑器中创建一个新表Category_new -

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"

这是您的最终输出-