HOME > Q&A > Excel （一般機能）

Excel （一般機能）
Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Home : Microsoft 365)
パワークエリのカスタム関数作成方法
投稿日時: 23/02/04 14:31:33
投稿者: chibikko

excelのパワークエリで、出身地列データ(市区町村迄の住所)を都道府県名のみのデータに置換したく、＜１＞と＜２＞のコードを作って見たのですが、どちらも思う様にならず困っています。
新列ではなく、元列への上書きで処理を終了したいのです。
コードのどこを修正すれば良いのでしょうか。
 
＜１＞
出身地 as text) =>
let
    ソース = Excel.CurrentWorkbook(){[Name="出身地"]}[Content],
    置き換えられた値 = Table.ReplaceValue(ソース,"東京都","東京都−",Replacer.ReplaceText,{"出身地"}),
    置き換えられた値1 = Table.ReplaceValue(置き換えられた値,"北海道","北海道−",Replacer.ReplaceText,{"出身地"}),
    置き換えられた値2 = Table.ReplaceValue(置き換えられた値1,"府","府−",Replacer.ReplaceText,{"出身地"}),
    置き換えられた値3 = Table.ReplaceValue(置き換えられた値2,"県","県−",Replacer.ReplaceText,{"出身地"}),
    出身地1 = Table.TransformColumns(置き換えられた値3, {{"出身地", each Text.BeforeDelimiter(_, "−"), type text}})
in
    出身地1
と
＜２＞
(出身地 as text) =>
let
    ソース = "出身地",
    テーブルに変換済み = #table(1, {{ソース}}),
    #"名前が変更された列 " = Table.RenameColumns(テーブルに変換済み,{{"Column1", "出身地"}}),
    置き換えられた値 = Table.ReplaceValue(#"名前が変更された列 ","東京都","東京都−",Replacer.ReplaceText,{"出身地"}),
    置き換えられた値1 = Table.ReplaceValue(置き換えられた値,"北海道","北海道−",Replacer.ReplaceText,{"出身地"}),
    置き換えられた値2 = Table.ReplaceValue(置き換えられた値1,"府","府−",Replacer.ReplaceText,{"出身地"}),
    置き換えられた値3 = Table.ReplaceValue(置き換えられた値2,"県","県−",Replacer.ReplaceText,{"出身地"}),
    区切り記号の前の抽出されたテキスト = Table.TransformColumns(置き換えられた値3, {{"出身地", each Text.BeforeDelimiter(_, "−"), type text}}),
    出身地1 = 区切り記号の前の抽出されたテキスト{0}[出身地]
in
    出身地1
回答
投稿日時: 23/02/04 15:40:13
投稿者: んなっと

まずはカスタム関数などを使わない方法を完成させましょう。
以下はあくまで例です。"Column1"のところは、"住所"などそちらの列名に置き換えてください。
 
 
let
    ソース = Excel.CurrentWorkbook(){[Name="出身地"]}[Content],
    Add = Table.TransformColumns(ソース, {{"Column1", each Text.Start(_,
       if Text.Contains("都道府県",Text.Middle(_,2,1)) then 3 else 4)}})
in
    Add
 
とか
 
let
    ソース = Excel.CurrentWorkbook(){[Name="出身地"]}[Content],
    Add = Table.TransformColumns(ソース, {{"Column1", each Text.Start(_,
       if List.Contains({"街道","宰府","諸県"},Text.Middle(_,1,2)) then 0 else
       if Text.Contains("都道府県",Text.Middle(_,2,1)) then 3 else
       if Text.Middle(_,3,1)="県" then 4 else 0)}})
in
    Add
投稿日時: 23/02/04 19:25:12
投稿者: chibikko

んなっと さんの引用:
まずはカスタム関数などを使わない方法を完成させましょう。
以下はあくまで例です。"Column1"のところは、"住所"などそちらの列名に置き換えてください。
 
 
let
    ソース = Excel.CurrentWorkbook(){[Name="出身地"]}[Content],
    Add = Table.TransformColumns(ソース, {{"Column1", each Text.Start(_,
       if Text.Contains("都道府県",Text.Middle(_,2,1)) then 3 else 4)}})
in
    Add
 
とか
 
let
    ソース = Excel.CurrentWorkbook(){[Name="出身地"]}[Content],
    Add = Table.TransformColumns(ソース, {{"Column1", each Text.Start(_,
       if List.Contains({"街道","宰府","諸県"},Text.Middle(_,1,2)) then 0 else
       if Text.Contains("都道府県",Text.Middle(_,2,1)) then 3 else
       if Text.Middle(_,3,1)="県" then 4 else 0)}})
in
    Add

 
カスタム関数を使わない方法ではステップが５つになる為、１ステップの関数化出来ないかと試行錯誤しています。
 
２つのコードを試してみましたが、新たなクエリが出来てしまいました。
またletの上に(出身地 as text)＝＞の追加も試しましたが、こちらはカスタム関数の呼び出しからの為新たな列が追加され「出身地」列の展開をする事になります。
どちらも、思っている動作ではありませんでした。
回答
投稿日時: 23/02/04 21:30:38
投稿者: んなっと

先程の方法で一発で変換できますよ。ステップが5つにはなりません。
回答
投稿日時: 23/02/04 21:50:00
投稿者: んなっと

テーブルをPowerqueryに読み込んだだけの最初の状態で
詳細エディターがどうなっているのか貼り付けてください。
回答
投稿日時: 23/02/04 22:06:45
投稿者: んなっと

あと、住所がある列名も。
投稿日時: 23/02/05 07:54:39
投稿者: chibikko

んなっと様
 
テーブルをPowerqueryに読み込んだだけの最初の状態の詳細エディタは
let
    ソース = Excel.Workbook(File.Contents("C:………\出身地.xlsx"), null, true),
    出身地_Table = ソース{[Item="出身地",Kind="Table"]}[Data],
    変更された型 = Table.TransformColumnTypes(出身地_Table,{{"出身地", type text}})
in
    変更された型
で、列名は「出身地」です。
回答
投稿日時: 23/02/05 08:32:21
投稿者: んなっと

let
    ソース = Excel.Workbook(File.Contents("C:………\出身地.xlsx"), null, true),
    出身地_Table = ソース{[Item="出身地",Kind="Table"]}[Data],
    変更された型 = Table.TransformColumnTypes(出身地_Table,{{"出身地", type text}}),
    Add = Table.TransformColumns(変更された型, {"出身地", each Text.Start(_,
       if List.Contains({"街道","宰府","諸県"},Text.Middle(_,1,2)) then 0 else
       if Text.Contains("都道府県",Text.Middle(_,2,1)) then 3 else
       if Text.Middle(_,3,1)="県" then 4 else 0)})
in
    Add
  
  
以下、余計な情報です。
●現在のクエリ内で関数を定義する方法
  
let
　　Ken = (t as text) => Text.Start(t,
        if List.Contains({"街道","宰府","諸県"},Text.Middle(t,1,2)) then 0 else
        if Text.Contains("都道府県",Text.Middle(t,2,1)) then 3 else
        if Text.Middle(t,3,1)="県" then 4 else 0),
    ソース = Excel.Workbook(File.Contents("C:………\出身地.xlsx"), null, true),
    出身地_Table = ソース{[Item="出身地",Kind="Table"]}[Data],
    変更された型 = Table.TransformColumnTypes(出身地_Table,{{"出身地", type text}}),
    Add = Table.TransformColumns(変更された型, {"出身地", each Ken(_)})
in
    Add
  
●別にカスタム関数を作成して呼び出す方法
関数の作成→新しい関数名　Ken →詳細エディターで
  
let
    s = (t as text) => Text.Start(t,
        if List.Contains({"街道","宰府","諸県"},Text.Middle(t,1,2)) then 0 else
        if Text.Contains("都道府県",Text.Middle(t,2,1)) then 3 else
        if Text.Middle(t,3,1)="県" then 4 else 0)
in
    s
  
と定義してから、現在のクエリをこんな感じで書き換える。
  
let
    ソース = Excel.Workbook(File.Contents("C:………\出身地.xlsx"), null, true),
    出身地_Table = ソース{[Item="出身地",Kind="Table"]}[Data],
    変更された型 = Table.TransformColumnTypes(出身地_Table,{{"出身地", type text}}),
    Add = Table.TransformColumns(変更された型, {"出身地", each Ken(_)})
in
    Add
...無駄が多い。
投稿日時: 23/02/05 10:12:07
投稿者: chibikko

中々うまくいかない為、カスタム関数化は諦めました。
ありがとうございました。