英文:
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"
remove the
, Comparer.OrdinalIgnoreCase
part if you want the check to be case-specific so that GAB<>gab
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论