Excel活用

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

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

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

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

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

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

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

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

none
none
イッツマニアック!

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

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

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

エラーになる原因

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

「” ”」がすべて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
    削除された列

利用上の注意点

このクエリをちゃんと動かすには注意点があります。

それは、データの「すべて更新」ボタンは使えないことです。

ここで概要をもう一度。

これは、サンプルテーブルから列タイトルを取り込むということをやっているので、ここが更新できていないと列タイトルの取り込みがうまくいかないのです。

なので、まずサンプルクエリの更新を実行してから月次損益比較クエリの更新を実行しないといけない、ということに注意してください。

まとめ

今回のポイントのおさらいです。

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

列タイトルが変わるとなぜエラーになるのか、そしてその回避策についてまとめてみました。

実はこれだけではまだ解決していない問題があるのですが。。。

次回、「更新したらSUMIF使えなくなっちゃった!」をお届けします。

最後までありがとうございました。

ブログランキング・にほんブログ村へ にほんブログ村 経営ブログ 財務・経理へ にほんブログ村 士業ブログへ にほんブログ村 士業ブログ 公認会計士へ

こちらもぽちっとお願いします!