[Power Query] for ループのような処理を Power Query で行う方法

connpass や Doorkeeper の REST API を利用してIT勉強会のデータを取得して、Excel であれば JSON データを表形式のテーブルに展開し Pivot テーブルに取りこんで分析する、Power BI であれば、ファクトテーブルとして取りこんでレポート、ダッシュボード作成に利用する、などを過去のブログでも紹介しています。その中で既に使っているやり方なんですが、意外にこの方法で悩んでいる方もいらっしゃるので、まとめて説明してみます。おおまかな手順は以下です。1) APIを使ったデータ取得をパラメータを受け取って処理する関数にする2) 渡したいパラメータ（for の 1 から n に相当）の要素をもったテーブルを作る3) 要素をパラメータとして関数に渡し、結果をとる4)...

Read More

[Power Query] OneDrive パーソナル (onederive.live.com) の Excel ブックに接続する方法

Power BI は OneDrive for Business (中身は SharePoint で、URL に my.sharepoint.com が含まれるもの) のフォルダへの接続を前提にしているようで、サービス名が同じでややこしいのですが、個人が使う OneDrive (URL に onedrive.live.com が含まれるもの)への接続は「簡単には」許していないように思えます。それはもう7年も前のことで、以下のような投稿をしています。このときは OneDrive パーソナル（と便宜上呼びます）への接続方法を見つけることができませんでした。（2015年当時）Power Query から https 経由で SharePoint の Excel ブックを開くhttps://road2cloudoffice.blogspot.com/2015/07/power-query-https-sharepoint-excel.htmlGoogleスプレッドシート(ベータ)に接続する最近...

Read More

[Power BI] カスタム関数をつかったQueryを含むデータセットが自動更新できない

(追記: Excel のブックをそのまま Power BI Service にあげて使用するのではなく、Power Query + Excel Pivot Table レポートを Power BI Desktop でレポートを書き換えています。Power Query クエリの多くをそのまま移行できるのは、モダンExcel + Power BI の組み合わせの使い勝手の良いところです）データを元にしたレポートやダッシュボードを複数のユーザーと定期的に共有することは、Excel で工夫・時間をかけて実現できますが、Power BI Service を含む Power BI は、その目的のためにつくられたこともあり、Excel での苦労や時間を軽減してくれます。特に、モダンExcel な使い方をしているユーザーにとって魅力に見えるのは、レポートのデータ更新の自動化...

Read More

[Excel BI/Power Query] そのフィルター大丈夫ですか？そのピボットテーブル レポート本当に正しいですか？

かなり前に書きかけていた記事を追記・完成させて公開します。Excel は大量のデータから欲しいデータを絞り込み、集計をするのは得意とするところですし、それを実現するために、SUMIFS 関数などの「数式・関数」を使ったり、オートフィルターやテーブル機能などの「機能」を使ったり、VBA を使ったプログラミングで実現するなど、さまざまな方法を使うことができます。それらの中でも「ピボットテーブル」は本当によく使います。 今回データを「絞り込む」ということに関して、以前、私自身がはまって、その理由が小一時間わからず、あらためて Excel の機能の特性を勉強させられたので、共有したいと思います。特に、ピボットテーブルと取得と変換 (Power Query) のクエリエディタを組み合わせて使っている方は、データの絞り込みの仕組みの違いを意識していないと、以下のような思わぬところで落とし穴にはまるかもしれません。 データソースからデータを絞り込む方法...

Read More