Power BI: 在一个字符串中查找并返回新列的特定值

huangapple go评论56阅读模式
英文:

Power BI: lookup and return for a new column specific values in a String

问题

I have a column with labels from the Microsoft Planner, similar to this example:

Labels
GAB;TIC;COMP;AQUI
TIC;COMP;LET
TIC;LET;PD
AQUI;PD;GAB

Suppose that GAB and LET are acronyms for departments, and I need to create a column that indicates which department owns that line. The result should be like this:

Department
GAB
LET
LET
GAB

How can I do this in Power BI (or query)?

I thought of something using LOOKUPVALUE or trying to split the string with the delimiter ";" but it's not working. Any ideas?

Thanks,
Breno

英文:

I have a column with labels from the Microsoft Planner, similar with this example:

Labels
GAB;TIC;COMP;AQUI
TIC;COMP;LET
TIC;LET;PD
AQUI;PD;GAB

Suppose that GAB and LET are acronym for departments and I need to create a column that indicates which department own that line. The result must be this:

Department
GAB
LET
LET
GAB

how can I do this at power bi (or query)?

I thought in something using lookupvalue or trying to split the string with the marker ";", but is not working. Some idea?

Thanks

Breno

答案1

得分: 1

let Source = #table({"Labels"}, {{"GAB;TIC;COMP;AQUI"}, {"TIC;COMP;LET"},{"TIC;LET;PD"},{"AQUI;PD;gab"}}),
FindList = Table.Buffer(#table({"Keyword"}, {{"GAB"}, {"LET"}})),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Labels", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Select(FindList[Keyword], (x) => Text.Contains([Labels], x, Comparer.OrdinalIgnoreCase))),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Department", each Text.Combine([Custom], ", ")),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom"})
in #"Removed Columns"

英文:

In powerquery you can try this to look for specific items on each row

let  Source = #table({"Labels"}, {{"GAB;TIC;COMP;AQUI"}, {"TIC;COMP;LET"},{"TIC;LET;PD"},{"AQUI;PD;gab"}}),
FindList = Table.Buffer(#table({"Keyword"}, {{"GAB"}, {"LET"}})),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Labels", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Select(FindList[Keyword], (x) => Text.Contains([Labels], x, Comparer.OrdinalIgnoreCase))),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Department", each Text.Combine([Custom], ", ")),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom"})
in #"Removed Columns"

Power BI: 在一个字符串中查找并返回新列的特定值

remove the

, Comparer.OrdinalIgnoreCase

part if you want the check to be case-specific so that GAB<>gab

huangapple
  • 本文由 发表于 2023年4月6日 22:47:40
  • 转载请务必保留本文链接:https://go.coder-hub.com/75950865.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定