基于if语句的Power Bi-DAX度量索引匹配

Question

我有两个表两个表，表1名叫"Data“，表2名叫"Report”。

表2(报表)名为“项目”、“水果列表”、“区域代码”、“语言代码”、“销售代码”()连接“”和"status“的标题名称。有时在表2中，根据标题有空格列。在这里输入图像描述

表1(数据)名为“"Concatenate"列表”、“区域代码”、“语言代码”、“销售代码”和“串联” 在这里输入图像描述的头名称为两个表创建了列，以便合并查询(表2到表table1)。我之所以不直接合并该表，是因为它将在Table2(Report)中创建大量的复制列。

在基于"Concatenate"列合并表之后，我为状态列创建了if条件--“是”或“否”。

在这里输入图像描述

现在在这里实现我想要实现的目标，根据表2(Report)中的标题“List1”、"Area Code1“、"Language Code1”、"Sales Code1“填充附加状态列-- "ERROR”或"NO ERROR“。

如果状态列为“是”，则根据表2(Report)中的“List1”、"Area Code1“、"Language Code1”、"Sales Code1“返回输出”无错误“。

如果状态列为"No“，则根据表2(Report)中的标题”果树List1“、"Area Code1”、"Language Code1“、"Sales Code1”返回输出“Code1”。列H:K为我的输出列。

在这里输入图像描述

文件附在这里，供您参考。

https://www.dropbox.com/s/rbb71gsjqq39uv8/INDEX%20MATCH%20MULTIPLE%20RESULT.pbix?dl=0 https://www.dropbox.com/s/m1onkdmlmfryjmx/INDEX%20MATCH%28OK%2CYES%29.xlsx?dl=0

Answer 1

您需要在Power查询编辑器中进行一些转换才能实现所需的输出。下面是高级编辑器的代码-

let
    Source = Excel.Workbook(File.Contents("C:\Users\Khademur Rabbani\Downloads\INDEX MATCH(OK,YES).xlsx"), null, true),
    REPORT_Sheet = Source{[Item="REPORT",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(REPORT_Sheet, [PromoteAllScalars=true]),
    #"Removed Blank Rows" = Table.SelectRows(#"Promoted Headers", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Blank Rows",{"REPORT.CONCATENATE", "DATA.CONCATENATE", "STATUS", "FRUIT LIST_1", "AREA CODE_2", "LANGUAGE_3", "SALES CODE_4"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"FRUIT LIST", type text}, {"AREA CODE", type text}, {"LANGUAGE", type text}, {"SALES CODE", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "FRUIT LIST", "AREA CODE", "LANGUAGE", "SALES CODE"}),
    #"Merged Queries" = Table.NestedJoin(#"Reordered Columns", {"FRUIT LIST"}, #"DATA (2)", {"FRUIT LIST"}, "DATA (2)", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "Custom", each [#"DATA (2)"][FRUIT LIST]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each List.Max([Custom])),
    #"Merged Queries1" = Table.NestedJoin(#"Added Custom1", {"FRUIT LIST", "AREA CODE"}, #"DATA (2)", {"FRUIT LIST", "AREA CODE"}, "DATA (2).1", JoinKind.LeftOuter),
    #"Added Custom2" = Table.AddColumn(#"Merged Queries1", "Custom.2", each [#"DATA (2).1"][AREA CODE]),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.3", each List.Max([Custom.2])),
    #"Merged Queries2" = Table.NestedJoin(#"Added Custom3", {"FRUIT LIST", "LANGUAGE"}, #"DATA (2)", {"FRUIT LIST", "LANGUAGE"}, "DATA (2).2", JoinKind.LeftOuter),
    #"Added Custom4" = Table.AddColumn(#"Merged Queries2", "Custom.4", each [#"DATA (2).2"][LANGUAGE]),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Custom.5", each List.Max([Custom.4])),
    #"Merged Queries3" = Table.NestedJoin(#"Added Custom5", {"FRUIT LIST", "SALES CODE"}, #"DATA (2)", {"FRUIT LIST", "SALES CODE"}, "DATA (2).3", JoinKind.LeftOuter),
    #"Added Custom6" = Table.AddColumn(#"Merged Queries3", "Custom.6", each [#"DATA (2).3"][SALES CODE]),
    #"Added Custom7" = Table.AddColumn(#"Added Custom6", "Custom.7", each List.Max([Custom.6])),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom7",{"DATA (2)", "Custom", "DATA (2).1", "Custom.2", "DATA (2).2", "Custom.4", "DATA (2).3", "Custom.6"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Custom.1", "FRUIT LIST 1"}, {"Custom.3", "AREA CODE 2"}, {"Custom.5", "LANGUAGE 3"}, {"Custom.7", "SALES CODE 4"}}),
    #"Added Custom8" = Table.AddColumn(#"Renamed Columns", "STATUS", each if 
    [FRUIT LIST 1] <> null 
    and [AREA CODE 2] <> null 
    and [LANGUAGE 3] <> null 
    and [SALES CODE 4] <> null then "NO ERROR" 
else "ERROR"),
    #"Reordered Columns1" = Table.ReorderColumns(#"Added Custom8",{"Index", "FRUIT LIST", "AREA CODE", "LANGUAGE", "SALES CODE", "STATUS", "FRUIT LIST 1", "AREA CODE 2", "LANGUAGE 3", "SALES CODE 4"}),
    #"Added Custom9" = Table.AddColumn(#"Reordered Columns1", "FRUIT_LIST_1", each if [FRUIT LIST 1] = null then "ERROR" else "NO ERROR"),
    #"Added Custom10" = Table.AddColumn(#"Added Custom9", "AREA_CODE_2", each if [AREA CODE 2] = null then "ERROR" else "NO ERROR"),
    #"Added Custom11" = Table.AddColumn(#"Added Custom10", "LANGUAGE_3", each if [LANGUAGE 3] = null then "ERROR" else "NO ERROR"),
    #"Added Custom12" = Table.AddColumn(#"Added Custom11", "SALES_CODE_4", each if [SALES CODE 4] = null then "ERROR" else "NO ERROR"),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom12",{"FRUIT LIST 1", "AREA CODE 2", "LANGUAGE 3", "SALES CODE 4"})
in
    #"Removed Columns2"

这是输出-

​

​

作为代码/步骤是巨大的，请从这个链接中找到报告文件。请检查表REPORT (2)和DATA (2)的转换