Power Query_Fundamentals

Power Queryの紹介 セルフサービスETL 2024年7月

スピーカー紹介 帥 暎琦（スイ エイキ） Fabric CAT(旧Power BI CAT) Customer Advisory Team  Microsoft Fabricの製品チーム  前職はデータアナリスト  グローバルでFabricのサポート  日本・中国・韓国・台湾・東南アジアの一部等

アジェンダ 01 02 Power Queryの概要 Power Queryの設定 03 04 Power Queryデモ 05 06

Power Queryの概要

Power Queryについて データの取得と変換（ETL）を可能 にしてくれるPower Queryに関する 基礎を習得することは非常に重要 である Power QueryはセルフサービスBIを 習得する上で最も基本的なスキル

用語・ニュアンスの確認 ⚫ Power Query • 基本は英語表記 • パワークエリでもOK これらのニュアンスはPower Queryを使用していくと理 解できるようになる • PQと略すことがあるが、パワクで略すとダサイ • Power Query Onlineはクラウド版Power Query • “Power Queryのような体験”と表現した場合、セルフサービスETL（取得と変換）を意味する • Power Query OnlineはPQOと略すことがある • PQOはデータフローとほぼ同義で使われる • データフローはオブジェクト（アーティファクト or アイテム）であり、1つまたは複数のテーブルの塊 • データフローはデータソースとなり得るが、Power Queryはそれを実現するための機能 • Power BIやExcelのPower QueryをPower Query Desktopという • Dataflow Gen1 (Power BI Dataflow) と Dataflow Gen2 (Fabric Dataflow) が存在する（後述）

• https://marshal115.hatenablog.com/entry/2023/01/16/090754
• https://marshal115.hatenablog.com/entry/2023/06/19/230347#Dataflow-Gen2

用語・ニュアンスの確認（続） ⚫ データフロー（Gen1）はデータソース 自分が作ったクエリ は自分しか利用で きない データフローはデータ ソースとして組織で 再利用可能

用語・ニュアンスの確認（続） ⚫ Dataflow Gen1 vs Dataflow Gen2 データソース扱い データ書き込み

これまでのデータ処理 上級ユーザーほど、処理能力の限界を実感 ⚫ Excelによる可視化までの作業 ⚫ データ処理時の課題  データをニーズに合わせて変換  初期データが汚い  データセットの追加（時系列データ等）  データ量が多く、更新に時間を要する  データセットの結合（VLOOKUP等に  反復作業が多い よるデータマッチング）  データの視覚化（分析用チャート等）

古典的な解決法 習得までに時間コストが非常に高い ⚫ データ処理方法 殆どの企業の現状＆惨状  関数や手動調整: データ抽出、ロジック計算、etc  VBA: 自動化を専門（マクロ） そのうちメンテが出来なくなり、レガシー化  SQL: データベースの神様。IT部門によって構築＆データベースに対する理解が必要 ⚫ データ処理の問題点 “ラグジュアリー品”であり、余裕がない企業も多い  問題解決＆テクニックをマスターするまでに時間を要する Power QueryによるETLの概念

Power Queryの使用 ⚫ BIツール（Power BI, Excel）でレポートを構築する流れ 企業が保有する多種 データのETL データモデルの 多様なデータ （抽出・変換） 構築 基データ ETL* 分析モデル * ETL: Extract, Transform, Load（データの取得と変換）の略 可視化・分析に基づ く意思決定 レポーティング インサイト

Power Query（PQ）とは？ ⚫ セルフサービスETL（データ加工機能） ✓ Extract: 抽出 ✓ Transform: 変換 ✓ Load: 出力 ETL ◼ Power Query ⚫ PQの特徴 ✓ ExcelやPower BIに搭載されたデータ加工機能（※PQOはクラウドサービスに搭載） ✓ Excelの通常作業と同じ操作感覚 ✓ ユーザーインターフェース（UI）操作でデータ処理の殆どを完結可能 ✓ Power Query Desktop と クラウドで連携が簡単にできる（コピペ等） ✓ 簡単・強力・奥深い（使えば使う程、その”凄さ” に感動） ✓ データ抽出・加工の“デファクト・スタンダード”（使えないと仕事にならない！）

Power Query処理に関する留意点 ⚫ Power Query Desktop操作時の処理パフォーマンスについて（※重要） ✓ 処理速度はパソコンのスペックに左右される（下記スペック（2022年時点）はGreat） RAM = Random Access Memory 机の大きさと考えてもらえればよ い。机が大きいほど、格納できるも のが多くなる ✓ プロセッサ（CPU）: 最新世代ほど良い ✓ RAM = 8GBは正直、足りない・・・（最低でも16GBほしい） ✓ Excelは64bit版がベスト（32bit版は大量データの場合、メモリ不足に） ✓ RAMが同じでも、処理パフォーマンスはデスクトップ > ノートPC（低電力） ✓ アクセスするデータソースによって、処理速度が変わる （例：データベース > CSV > Excel > ウェブ….）

Power Queryエディタの画面: Excel版 ① リボン ③ 数式バー ② ナビゲーション ⑤ クエリ設定画面 ④ プレビュー画面 このエディタを中心に複数 のソースデータの抽出・変 換・結合を行う ExcelとPower BIの両方に 搭載されており、Excelを使 い慣れている人はExcelから 学んだほうが入りやすいかも データフロー Power Query Online

Power Query Tips • 1つのアプリケーションで使い方を覚えれば、他でも通用する • Power BI DesktopやExcelでクエリを作り、Dataflow側へ張り付 けるやり方が効率良い • Dataflow Gen1はOneLakeへデータをDelta Parquetとして書き込 む機能は持っていない • Dataflow Gen2はFabric容量がないと使用できない。Dataflow Gen1はPower BI Proがあれば使用可能 例: ExcelでPower Queryを覚えれば、Power BIでもFabricでも学習コストは ほぼゼロ Dataflow Gen1の解説

• https://marshal115.hatenablog.com/entry/2023/05/03/174414

クエリとは ⚫ クエリとは問い合わせや命令のこと また、これによって得られた結果もクエリと呼ばれ、Power Queryではテーブルやリスト等 ✓ （例1）直近1ヶ月の店舗別・分類別売上を抽出せよ （Power Queryからスプレッドシートへ出力） ✓ （例2）販売商品の在庫・売上・利益率を算出するためのデータセットを作るには？ （Power QueryからBI用のデータセットの準備） ✓ （例3）販売実績 vs 計画のWeekly比較を行うには？ （Excelで作った予算 vs CSVファイルの実績の比較、等） Power Queryはあらゆるデータソースから必要なデータだけを抽出し、データ粒度を変更した り、分析可能なデータ仕様に変更するための最強のETL機能

Power Queryの学習曲線 Benefit Power Query データ整備に関しては Power Query一択 ※VBA（マクロ）は必要 なし Visual Basichc for Application (VBA) Excel functions Years Source： M is for Data Monkey

Power Queryによる効率化: 時間削減の実例 ⚫ アナリスト案件（在庫評価）に際しての時間削減効果 単位： 分 作業項目 売上高作成 完成品在庫作成 パーツ在庫作成 経費作成 仕掛品・原材料抽出 合計 時間（分） 通常時 120 80 30 20 10 260 PQ使用時 削減率 10.0 ▲92% 10.0 ▲88% 1.0 ▲97% 1.0 ▲95% 1.0 ▲90% 23.0 ▲91% Power Queryを初めて使い始めた時 （2017年1月）の在庫評価案件 時間削減率 300 260 250 200 150 100 91% 50 実際に使った人間にしか分からないその衝撃・・ これでキャリアが変わる人も 23.0 0 通常時 PQ使用時

Power Queryの設定

Excel Power Queryの設定 Power QueryはExcelが元祖であり、機能的には Power BI DesktopのPower Queryとほぼ同じ Excelを使い慣れている人はまずExcel版のPower Queryで試すと良い Power Queryの基礎_設定編

• https://marshal115.hatenablog.com/entry/2020/06/01/140255

初期設定① ⚫ Microsoft Officeを最新バージョンへ更新（Windows 10） ✓ Windowsキーをクリックし、「更新」 > 「詳細オプション」 > 一番上を「オン」 Officeが最新バージョンになっ ていない場合、最新の機能が 使用できない可能性あり

初期設定② ⚫ データタブからPQを起動 > クエリエディタの設定 Officeが最新バージョンに更新 されたことを確認し、①～④ の手順でPQの初期設定を行う

初期設定③ ⚫ Power Queryのプライバシーレベルの設定 ✓ プライバシーの設定は「グローバル」環境で常に「オフ」設定にするかどうか判断 ✓ 当該設定はクエリのパフォーマンスに影響するため、必須事項である 異なるデータソースをマージする際、Formula Firewallというエラーが出現することがあり、 これによってクエリにエラーが発生してしまう。 外部ソースにデータを送信してしまわないよう、 PQエンジンの判断によるものであるが、使 用するデータが内部の場合は「無視」でOK

初期設定④ ⚫ 地域の設定（クエリステップの言語設定） 【ステップ名について】 ① 操作の度に自動生成 ② ステップ名はステップを a) クリックで選択し b) F2で変更 ③ 言語は好み次第であるが、他の 人でも分かりやすい名称に変更して おくことを推奨

Excel 2016の場合のPower Queryの起動 ⚫ Excel 2016のPower Query画面 「データ」よりPower Queryへ 複数のデータを抽出する場合 には、「フォルダから」を選択。 該当フォルダ及びサブフォルダ 内にある抽出可能な全ての ファイルが表示される

設定メニューの比較 ⚫ Excel vs Power BIのクエリのオプション Power BIのほうが設定項 目が圧倒的に多い

設定メニューの比較 ⚫ Dataflowのオプション

初期設定① ⚫ 設定を開く

初期設定② ⚫ オプションをチェック プライバシーレベルは前述の通り、 内部のみのデータソースだけであれ ばこちらの設定でOK。 ただし、社内のデータガバナンス ルールに従うことが重要 残りの設定はExcel Power Queryを参照

Power Queryデモ

デモで行われる主な操作 ⚫ Power Queryの主な操作（カッコ内はM言語） – ソースフォルダへの参照（Folder.Files） これらの操作はPower Queryが UI操作で自動的に行ってくれる – クエリの参照 vs クエリの複製 – ファイルの中身の抽出（Excel.Workbook / Csv.Document, etc） – ファイルの結合（Table.Combine / Binary.Combine, etc） – グループ化（Table.Group） M関数を覚える必要はないが、 覚えておくと後から便利 – クエリ同士のマージ（VLOOKUPと同じ = Table.NestedJoin） – ヘッダーの昇格（Table.PromoteHeaders） – ピボットの解除（Table.UnpivotOtherColumns = 正規化の概念） – データ型の変更（Table.TransformColumnTypes） – 列の変換（Table.TransformColumns） – 列の選択（Table.SelectColumns）

デモ01: ソースデータの把握 ソースデータ分析をやってみよう！ フォルダ内のファイル名・サイズ・ 種類を取得 データはExcelで確認し、Power BIで可視化

デモ02: Excelをデータをソースとする Excelデータソースの特性 前提: 既存Excelは現在開いているExcel、新規Excelは既存Excelを参照している空のExcel 項目 既存Excel 参照 新規Excel / Power BI テーブル 参照 参照 名前付け範囲 参照 参照 データのみ 参照 参照 印刷範囲 参照 参照 参照エラー NA 既存Excelが開いている 既存Excelが開いていない データ参照可否 NA ワークシートに出力されたデータのみ = 接続専用への参照は

Power Queryのスキル別アドバイス L100-200 機能の確認: リボンのアイコン、クエリ・行・列を右クリック L300-400 UIに頼らない操作: M関数やステップ別の操作を理解 異なるデータ書式にチャレンジ L500～ 効率性の重視: カスタム関数の構築、クエリの最適化、モデ リング用データ構築、データ戦略

Microsoft ,会 社 名 ロゴ 自 動 的 に生 成 された説 明 Thank you! @marshal_dabao テクテク日記（Power BI, Fabric関連） https://aka.ms/techtech

• https://twitter.com/marshal_dabao
• https://aka.ms/techtech