はじめに
SharePointサイトに置いたJSON形式のログなどを、SharePointフォルダで取り込んで結合しようとした際に、なぜかCSVのようにうまくいきませんでした。いろいろ試して回避策を見つけましたので紹介します。
対象者
Power BIサービスでデータフローを使っている人向けです。
結論
JSONの結合はPower BI デスクトップを使ってローカルフォルダを参照した場合には問題なく自動処理できます。Power BI サービスのデータフローには、Power BI デスクトップとPower Queryで使われるM言語に互換性があるので、ローカルで作った式をそのままコピーしてしまおうというのが今回の目論見です。
準備
まずは実現したいことの準備をします。適当な同じ書式で作成したJSONファイルを、適当なSharePointサイトのドキュメントフォルダに保存しておきます。
ちなみに、今回はサンプルとして、「JSON サンプル」でググった際に最初に出てきたIBMのドキュメントで紹介されていた２つのJSONの書式をそのままメモ帳にコピーし、 1.json、2.jsonと名前を付けて保存したものを、SharePointサイトにアップロードしました。
まずは失敗例から
データフローを作成しSharePointフォルダを選択します。先ほど２つのJSONファイルをアップロードしたSharePointサイトのURLを接続設定のサイトURLにコピーし、認証の種類は組織アカウントで先に進みます。
ここで自動結合できればよいのですが、JSONファイルを保存したフォルダはもっと下層にあるので、とりあえず「データの変換」を選択します。
Folder Pathにフィルタをかけ、先ほどJSONファイルを保存したSharePointサイトのフォルダを選択します。
２つのJSONファイルが保存されているフォルダにたどり着いたので、JSONファイルが見えている状態になりました。
あれ？？ このJSONファイルだと結合に成功してしまいました・・・。綺麗なJSONすぎたのかしら。
ここから先は、この手順で失敗してしまった場合には・・・ってことでお願いします。
ブラウザでうまくいかなかったときは、ローカルでやるべし
さて、ブラウザで失敗したと仮定して、そんなときにはいったんJSONファイルをいくつかダウンロードしてローカルPCの適当なフォルダに保存しましょう。
データ取得から「フォルダ」を選択します。
JOSNファイルを保存したローカルのフォルダを指定します。
フォルダを指定したので、そこにあるJSONファイルだけがならんで表示されました。「結合」。ボタンをクリックし、「データの結合と変換」をクリックします。
２つのJSONファイルの内容が、いい感じに展開されました。「結合」ボタンで自動処理されて感激です。
ローカルで作成したクエリをクラウドに移植する
スクリーンショットを見ていただくとわかるように、JSONというクエリと、その内部で変換処理に使われたヘルパークエリが表示されています。このクエリはPower BI Desktopアプリで作成されたものですが、実はブラウザ上のPower BI サービスで作成するデータフローにコピー&ペーストできるのです。
クエリをコピーする際に、ヘルパークエリもまとめてコピーしたいので、折りたたんでCtrlをクリックしながら両方を選択して右クリック。「コピー」を選択します。
まず、JSONと名前の付いたクエリにマウスを持っていき、右クリックして「コピー」を選択します。
次に、ブラウザでデータフローのPower Query画面を開き（この画面が開けない場合は、とりあえず空のクエリを選択してそのまま次に進むと良いです）、「貼り付け」をクリックしてみます。
おっと残念。クエリの貼り付けはキーボードのCtrl+Vで行う必要があるようです。素直に従います。
貼り付けできました。何かエラーが出ていますが、この段階ではエラーが出ていますが問題ありません。なぜエラーになっているかは、JSONと名前の付いたクエリをクリックした状態で右クリックし、「詳細エディタ」を開いてみるとわかります。
一番先頭にあるソースにローカルのCドライブにあるフォルダが選択されているからです。ブラウザで開いているPower BIサービスからは、手元のPCのローカルフォルダは除きにいけませんから、エラーが出るのも当然です。
逆に言うと、JSONフォルダが置いてあるSharePointフォルダをここに持ってくれば解決です。
SharePointフォルダを指定するクエリを作る
ローカルのフォルダが指定してあるところに、直接SharePointフォルダを見に行く式を書いても良いのですが、ここは綺麗に読み込みだけを行う新しいクエリを作りましょう。
「データを取得」から、詳細の中にあるSharePointフォルダを選択します。
失敗例と同じように、JSONファイルを置いてあるSharePointサイトのURLを指定して先に進みます。Folder PathでJSONファイルが置いてあるフォルダに絞り込みをかけるところも同じです。
できたクエリの名前を適当な名前に変更します。今回は「SPO_JSON_Folder」にしました。この名前をCtrl + Cでコピーして覚えておきます。
いよいよ、エラーが出ていたJSONクエリを、「詳細エディタ」で開きます。先ほどエラーの原因になっていたローカルフォルダが指定されている部分「Folder.Files("C:\tmp\JSON")」をSharePointフォルダの指定に使ったクエリの名前「SPO_JSON_Folder」に差し替えます。
先ほどまでJSONというクエリに出ていたエラーが消えました。
サンプルファイルにはまだエラーが出ています。こちらも「詳細エディタ」で開いてみます。
やはりローカルPCのCドライブが指定されていますね。先ほどと同じように、SharePointフォルダの場所を取得している「SPO_JSON_Folder」に差し替えます。
これですべてのエラーが消えて、Power BI Desktopアプリで作成したクエリがデータフローに移植できました。
データセットからデータフローへの移植にも使える
この方法では、とりあえずPower BI デスクトップで作成したけれど、やっぱりデータフローで処理しておきたかった、というような場合に簡単に移植ができます。アプリとブラウザの間でクエリがコピーできるのが素晴らしいですね。ブラウザ上で処理がうまくできなかった場合に、ローカルにいくつかサンプルファイルを置いてテストを行うようなケースにも使える方法なので、ぜひ活用しましょう。