列タイトルが変わるデータをパワークエリで取得・更新する方法

今度のブログネタはこちら。ちょっとマニアックやけど、やってみたらできたのでブログにしようと思います。#PowerQuery #パワークエリ https://t.co/TRdM6JPiuO pic.twitter.com/YbY0X5iaXJ
— 酒井たかま@公認会計士 (@SakaiTakama) October 24, 2019

この前予告した内容について書いてみようと思います。

題して「列タイトルが変わるデータをパワークエリで取得・更新する方法」です！

none

え？どういうこと？
どんな場面の話にゃの？　

酒井たかま

よく分からないですよね。
まずはこちらのスライドをご覧ください。これが今回お伝えしたいことです。

注目すべきは列タイトルです。

[1907.csv]は列タイトルが7月、[1908.csv]は列タイトルが8月になっていて、[1907.csv]から[1908.csv]に更新するときにエラーが起きるのです。

ちなみにこれ勘○○行の科目別対比表をパワークエリで読み込むときに起きる問題なんですね。

none

イッツマニアック！

酒井たかま

…ですよね。笑
これに限らず、列タイトルが変わると更新するときにエラーになるので、その回避方法を知っておいてほしいと思います。

前置きが長くなりましたが、今日のポイントです。

更新前の列タイトルがエラーの原因
列タイトルを、取り込むデータに応じて更新する方法

Contents

エラーになる原因
列タイトルを取り込むデータに応じて更新する方法
- 列タイトルを更新するためのサンプルテーブルの作成
- 取り込んだ列タイトルを使ってテーブル作成する方法
利用上の注意点
まとめ

エラーになる原因

まずはエラーになる原因を見ておきましょう。

これが、1907.csvを読み込んだクエリで、1908.csvを読み込もうとした時に出るエラー画面です。

適用したステップを見ると、「変更された型」でエラーになったことが分かります。

では、次に詳細エディターでクエリの中身を見てみましょう。

エラーの原因は、「1908.csvには列タイトル[２０１９年７月発生]が無いから次に進めない」これです。

なので、解決策は「列タイトルが、データを読み込む都度に対応する列タイトルに更新する」となるのです。

列タイトルを取り込むデータに応じて更新する方法

取り組む手順はこんな感じです。

まず、取り込みたいデータから、列タイトルをサンプルテーブルとして取得します。いわゆる「箱を作る」というイメージです。

次に、このサンプルテーブルに取得した列タイトルを使ってデータを取得します。こちらは箱に対して「中身を入れる」といった工程になります。

列タイトルを更新するためのサンプルテーブルの作成

サンプルクエリは、データ取得のみでテーブル作成し、読み込みを実行してサンプルテーブルを作ります。

やることはせいぜいデータソースを相対パス化することぐらいでしょう。

この時、「月次損益比較」クエリとソースデータを合わせておいてください。同一データを使わないと、正しく列タイトルが取得できないためです。

サンプルデータができれば、次は月次損益比較テーブルの作成です。

取り込んだ列タイトルを使ってテーブル作成する方法

ここでの主な作業は列タイトルを相対パス化していくことです。

詳細エディターを見てみましょう。

解説、と言っても見てのとおりなのですが、ここでのポイントは一つだけ。

”　”　を相対パスで置き換える。

修正前のエラーが出るクエリとよく見比べてください。これが修正前です。

「”　”」がすべてfPathやCxxに置き換わっているのが分かると思います。

これで、読み込めば今回の月次損益比較テーブルの作成は完了です。

ちなみに今回作ったクエリの詳細はこちら。

***サンプル***
let
    fPath = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content]{0}[列1],
 
    ソース = Csv.Document(File.Contents( fPath ),
            [Delimiter=",", Columns=5, Encoding=65001, QuoteStyle=QuoteStyle.None]),

    型の変更 = Table.TransformColumnTypes(ソース,
              {{"Column1", type text}, {"Column2", type text}, 
              {"Column3", type text}, {"Column4", type text}, 
              {"Column5", type text}})

in
    型の変更


***月次損益比較***
let
    fPath = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content]{0}[列1],
   
    C2 = Excel.CurrentWorkbook(){[Name="サンプル"]}[Content]{0}[Column2],
    C3 = Excel.CurrentWorkbook(){[Name="サンプル"]}[Content]{0}[Column3],
    C4 = Excel.CurrentWorkbook(){[Name="サンプル"]}[Content]{0}[Column4],
    C5 = Excel.CurrentWorkbook(){[Name="サンプル"]}[Content]{0}[Column5],
    
    ソース = Csv.Document(File.Contents( fPath ),[Delimiter=",", Columns=5,
            Encoding=65001, QuoteStyle=QuoteStyle.None]),
    
    昇格されたヘッダー数 = Table.PromoteHeaders(ソース, [PromoteAllScalars=true]),
    
    変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"科目名", type text},
                  { C2 , Int64.Type}, { C3 , Int64.Type}, 
                  { C4 , Int64.Type}, { C5 , Int64.Type}}),
    
    削除された列 = Table.RemoveColumns(変更された型,{ C4 })
in
    削除された列