英文:

Filter duplicate rows in powerbi table based on language

问题

你想在Power BI中过滤掉那些id出现超过一次的行，根据以下条件进行过滤：

如果行的语言是"fi"，则保留该行。如果找不到"fi"语言，则保留"en"语言。如果都找不到，则保留"se"语言。因此，表中的结果行将是1、2、5、6。

有办法像这样进行筛选吗？我在Power BI中还比较新手，迄今为止还没有成功实现这个。

【注意：这是你要求的翻译部分。】

英文:

I have the following table in Power BI:

index	id	language	value
1	12	fi	value_in_fi
2	312	fi	value_in_fi
3	312	en	value_in_en
4	312	se	value_in_se
5	22	fi	value_in_fi
6	1	se	value_in_se
7	22	en	value_in_en

I would like to filter out rows from the table where the id appears more than once, under the following conditions:

If the row's language is "fi," keep that row. If the language "fi" is not found, keep the language "en." If neither is found, keep the language "se." So, the resulting rows in the table would be 1, 2, 5, 6.

Is there any way to do filtering like this? I'm kind of new to powerbi so haven't succeeded in this so far

答案1

得分: 2

以下是你的翻译：

你可以在你的表格上应用一些额外的转换。以下是你的表格的Power Query代码。前往你的表格的高级编辑器，并使用下面的代码-

不要忘记在新代码的第1行更改previous_step_name。

    //.... 你现有的代码

    //新代码从这里开始    
    #"添加自定义" = Table.AddColumn(#"previous_step_name", "自定义", each if [language] = "fi" then 1 else if [language] = "en" then 2 else if [language] = "se" then 3 else 4),
    #"分组表格" = Table.Group(#"添加自定义", {"id"}, {{"最小值", each List.Min([自定义]), 类型 number}}),
    #"合并查询" = Table.NestedJoin(#"添加自定义", {"id", "自定义"}, #"分组表格", {"id", "最小值"}, "分组表格", JoinKind.Inner),
    #"删除列" = Table.RemoveColumns(#"合并查询",{"分组表格", "自定义"})
in
    #"删除列"```

以下是输出-

[![enter image description here][1]][1]


----

你的新代码将如下所示-

```let 
    Source = PostgreSQL.Database("localhost", "test"), 
    table_name = Source{[Schema="testschema",Item="table_name"]}[Data], 


    //新代码从这里开始    
    #"添加自定义" = Table.AddColumn(#"table_name", "自定义", each if [language] = "fi" then 1 else if [language] = "en" then 2 else if [language] = "se" then 3 else 4),
    #"分组表格" = Table.Group(#"添加自定义", {"id"}, {{"最小值", each List.Min([自定义]), 类型 number}}),
    #"合并查询" = Table.NestedJoin(#"添加自定义", {"id", "自定义"}, #"分组表格", {"id", "最小值"}, "分组表格", JoinKind.Inner),
    #"删除列" = Table.RemoveColumns(#"合并查询",{"分组表格", "自定义"})
in
    #"删除列"```

<details>
<summary>英文:</summary>

You can apply some additional transformation on your table. Here below is the **Power Query** code for your table. Go to **Advance Editor** of your table and use this below code-

Don&#39;t forget to change the **previous_step_name** in line 1 of the new code.

let
//.... your existing code

//New code starts here    
#"Added custom" = Table.AddColumn(#"previous_step_name", "Custom", each if [language] = "fi" then 1 else if [language] = "en" then 2 else if [language] = "se" then 3 else 4),
#"grouped_table" = Table.Group(#"Added custom", {"id"}, {{"min", each List.Min([Custom]), type number}}),
#"Merged Queries" = Table.NestedJoin(#"Added custom", {"id", "Custom"}, #"grouped_table", {"id", "min"}, "grouped_table", JoinKind.Inner),
#"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"grouped_table", "Custom"})

in
#"Removed Columns"

Here is the output-

[![enter image description here][1]][1]


----

Your new code will be like below-

let
Source = PostgreSQL.Database("localhost", "test"),
table_name = Source{[Schema="testschema",Item="table_name"]}[Data],

//New code starts here    
#"Added custom" = Table.AddColumn(#"table_name", "Custom", each if [language] = "fi" then 1 else if [language] = "en" then 2 else if [language] = "se" then 3 else 4),
#"grouped_table" = Table.Group(#"Added custom", {"id"}, {{"min", each List.Min([Custom]), type number}}),
#"Merged Queries" = Table.NestedJoin(#"Added custom", {"id", "Custom"}, #"grouped_table", {"id", "min"}, "grouped_table", JoinKind.Inner),
#"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"grouped_table", "Custom"})

in
#"Removed Columns"

  [1]: https://i.stack.imgur.com/wcjG2.png

</details>