如果Text包含關鍵字作為潛文本，則Text.Contains-有解無憂

我希望使用 Text.Contains 從所需列中顯示的句子中區分動物的性別：

如果 Text 包含關鍵字作為潛文本，則 Text.Contains

在這個稍微不尋常的情況下，男性包含在女性中，因此所有結果都回傳男性。

如何修改此代碼以實作此目的？

當前 M 代碼：

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([Column1], "male", Comparer.OrdinalIgnoreCase) then "Male" else null)
in
    #"Added Custom"

更新：

這是我目前的解決方案：

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Females", each if Text.Contains([Column1], "Female", Comparer.OrdinalIgnoreCase) then "Female" else null),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Males", each if Text.Contains([Column1], "Male", Comparer.OrdinalIgnoreCase) then "Male" else null),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Sex", each if [Females] = "Female" then "Female" else if [Males] = "Male" then "Male" else null),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom2",{"Column1", "Sex"})
in
    #"Removed Other Columns"

但是，我不太喜歡需要多個自定義列，并且覺得會有更優雅的解決方案。

uj5u.com熱心網友回復：

我建議將輸入拆分為一個串列物件并檢查串列是否包含“女性”或“男性”（或兩者都不包含）：

if List.Contains(Text.Split([column1]," "), "female") then "female" else if List.Contains(Text.Split([current]," "), "male") then "male" else null

或者，在M-Code：

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if List.Contains(Text.Split([column1]," "), "female") then "female" else if List.Contains(Text.Split([column1]," "), "male") then "male" else null)
in
    #"Added Custom"

如果 Text 包含關鍵字作為潛文本，則 Text.Contains

也許，如果您想冒檢索誤報的風險，請嘗試List.FindText：

List.First(List.FindText(Text.Split([column1]," "),"male"))

或在M-Code：

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"current", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.First(List.FindText(Text.Split([column1]," "),"male")))
in
    #"Added Custom"

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/472051.html

標籤：擅长分裂电源查询包含

上一篇：將特定列值復制到另一個作業簿

下一篇：R：如何為多個資料框添加邊框？