Power Query Fundamentals

Power Queryの紹介 セルフサービスETL 2025年3月 Eiki Sui

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

Power Queryの概要

Microsoft Fabric AI時代のデータアナリティクス: 2024年11月以降（最新 Data Factory Databases New! Analytics Industry Solutions OneLake Real-Time Intelligence Power BI ） Partner Solutions

Microsoft Fabric AI時代のデータアナリティクス: 2024年11月以降（最新 元々 Azure で PaaS として提供されていた Synapse および オペレーショナ ルDBであるSQL DB を Power BI と同じように SaaS 化して提供 ） Power BI はそのまま ISVによる 開発強化 Data Factory Databases New! Analytics Industry Solutions Real-Time Intelligence Power BI Partner Solutions データパイプライン オペレーショナル 分析ワークロード 業界ソリューション リアルタイム・ 時系列分析 BIツール パートナー開発 豊富なビジュアルを 使用して、分析情 報の検索、進捗状 況の追跡、意思決 定の迅速化を行う ISVによるAzure Marketplaceを通 じてFabric上でアプ リケーションを構築・ 拡張 市民寄り プロ寄り クラウド規模のデー タ移動とデータ変換 サービスを利用して、 最も複雑なデータ 統合とETLシナリオ を解決 シンプルで直感的、 かつ自律的なデータ ベースを数秒でプロ ビジョニング。AIアプ リケーション開発を 効率化し、革新的 なシナリオを創出 ETL,機械学習、 データウェアハ ウスの3つのワー クロードで構成 されるAnalytics ワークロード 業界モデルを活用 し、データストリーム を統合、規制遵守 を維持しつつ、賢い OneLake 意思決定を支援す る“単一の情報源” を構築 1GBから1PBまで 任意のデータソース と形式を迅速に取 り込み変換し、クエ リの実行、結果を 視覚化 市民寄り プロ寄り プロ寄り プロ寄り 市民寄り

Microsoft Fabric Data FactoryのDataflow Gen2にPower Query機能が含まれる Data pipeline Data Factory ETLジョブ等のワークフローを連携（オーケストレート）する Dataflow Gen2 市民開発者等、セルフサービス型ETLを可能にする データフローにはPower Queryという技術が使用される

Power Queryについて ビジネスユーザーでも簡単にデータの 取得と変換（ETL）を実現可能 Power QueryはセルフサービスBIを 習得する上で重要な基礎スキル SQLが得意な人はPower Query に感動する可能性が高い

用語・ニュアンスの確認 ⚫ Power Query • 基本は英語表記 • 言語名はM (Power QueryのエンジンはMashup Engine) これらのニュアンスはPower Queryを 使用していくと理解できるようになる • パワークエリでもOK • PQと略すことがあるが、パワクで略すと“ダサイ” • Power Query Onlineはクラウド版Power Query • “Power Queryのような体験”と表現した場合、セルフサービスETL（取得と変換）を意味する • Power Query OnlineはPQOと略すことがある • PQOはデータフローとほぼ同義で使われる（理由は下記） • Power BIやExcelのPower QueryをPower Query Desktopという • データフローはデータソースとなり得るが、Power Queryはそれを実現するための機能 • 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

Power BI Dataflow vs Fabric Dataflow ⚫ Dataflow Gen1 (DFG1) vs Dataflow Gen 2 (DFG2) https://learn.microsoft.com/en-us/fabric/data-factory/dataflow-gen2-migrate-fromdataflow-gen1 - DFG1 = Power BI Dataflow - DFG2 = Fabric Dataflow ⚫ 主な特徴 - DFG1はPower BIアイテムであり、Fabric Freeユーザーは作れない ※Pro (試用版) へのアップグレードを勧められる - DFG2はFabricアイテムであり、Fabric Freeユーザーでも作れる。Fabric容量 / PBI Premium 容量ワークスペースで作成可、Pro/PPUワークスペースでは不可 ※Fabric無料トライアルの開始を勧められる ⚫ Power BIユーザーだった場合、DFG1が多く残っている可能性が高い (移行推奨) ⚫ Fabricからスタートした場合、最初からDFG2を使うことを推奨

• https://learn.microsoft.com/en-us/fabric/data-factory/dataflow-gen2-migrate-from-dataflow-gen1
• https://learn.microsoft.com/power-bi/enterprise/service-premium-per-user-faq

Dataflow Gen1の図説 ⚫ Dataflow Gen1はデータソース 自分が作ったクエリ は自分しか利用で きない ソースデータのプレビューを 参照処理するだけ データフローはデータ ソースとして組織で 再利用可能 参照・処理機能を持ち、 処理済データがクラウド に残る

Dataflow Gen1とDataflow Gen2の図説 ⚫ Dataflow Gen1 vs Dataflow Gen2 DF Gen1 Semantic Model ⚫ Power BI ProやPPU だけで運用 ⇒ DFG1使用 データソース扱い Source 可 but 非推奨 ⚫ P1 or F SKUあり Lakehouse ⇒ DFG2を検討 DF Gen2 Direct Lake データ書き込み DF = Dataflow Warehouse Lakehouse/Warehouse がデータソース扱い ⚫ DF Gen1をソースに、 DF Gen2でLH/WHへ の書き込みも可能だが、 パフォーマンスが悪いた め非推奨

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の学習曲線 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 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） – クエリ同士のマージ（VLOOKUPと同じ = Table.NestedJoin） – ヘッダーの昇格（Table.PromoteHeaders） – ピボットの解除（Table.UnpivotOtherColumns = 正規化の概念） – データ型の変更（Table.TransformColumnTypes） – 列の変換（Table.TransformColumns） – 列の選択（Table.SelectColumns） M言語を覚える必要はないが、 良く使うものは覚えておくと便利

デモで行われる主な操作 ⚫ 関数の参照先 – 公式ドキュメント – Power Queryエディタ上で関数をレファレンス Power Queryエディタを立ち上げ > 空のクエリ > （数式バーで） =#shared と入力

• https://docs.microsoft.com/powerquery-m/power-query-m-function-reference

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

デモ02: 多数ファイルの結合 Excel / CSV ファイルの結合 C:¥Users¥Administrator¥OneDrive - 帥 ¥Microsoft¥Demo¥Power BI¥InvSales¥Raw ブログ参照 CSVの結合 Excelの結合

• https://marshal115.hatenablog.com/entry/2020/10/09/144529
• https://marshal115.hatenablog.com/entry/2021/04/05/102728

デモ02: 多数ファイルの結合 (続) ⚫ CSVの結合法 ① Binary.Combine ② Csv.Document ⇒ Table.PromoteHeaders ⇒ Table.Combine ③ SampleBinary + Parameter + SampleQuery + Custom Function ⚫ Excelの結合法 ① Excel.Workbook ② Excel.Workbook([Content], true, true) ⇒ Table.Combine ③ SampleBinary + Parameter + SampleQuery + Custom Function ⚫ おまけ（1つのシートだけを展開） ✓ Source[Content]{0}の使用

デモ03: その他応用 複数ヘッダーのピボット解除 C:¥Users¥Administrator¥OneDrive - 帥 ¥PQ¥Exercise¥PQ_03_UnpivotWithMultiHeaders

Power Query Tips

Power Queryでデータを扱う際の重要な考慮事項1 ⚫ Power Query エディターの UI にある各アイコンを理解する DFG1 DFG2

Power Queryでデータを扱う際の重要な考慮事項2 ⚫ クエリの整理整頓 ✓ クエリが増えすぎた場合に備えて、タイプ別に分ける ✓ フォルダ例: Fact, Dimension, Helper, Staging, Shema, Raw, etc

Power Queryでデータを扱う際の重要な考慮事項3 ⚫ 1つのアプリケーションで使い方を覚えれば、他でも通用する ✓ ExcelでPower Queryを覚えれば、Power BIでもFabricでも学習コストはほぼゼロ ✓ Power BI DesktopやExcelでクエリを作り、Dataflow側へ張り付けるやり方が効率良い ペースト時、必ず Ctrl + Vを使う必要あり 「資格情報」の再設定 が必要 DFG1, DFG2からPBI DesktopやExcelへの コピペも可能

Power Queryでデータを扱う際の重要な考慮事項4 ⚫ ソースのタイプとデータ加工の複雑さ ✓ 例: データベース◎、CSV◎、Excel〇、Excel複数シート△、Webコンテンツ 、等 ✓ インポートモードにおいて、セマンティックモデルへの読み込み速度はSQL > CSV > Excel ✓ 最も早いソース (SQL) は最も遅いソース (Excel) の4.6～5.8倍も早い * On-premise SQL Server, PC Spec RAM: 32GB, Core i7 – 1270P 2.2GHz Contoso DB: https://www.microsoft.com/download/details.aspx?id=18279

• https://www.microsoft.com/download/details.aspx?id=18279

Power Queryでデータを扱う際の重要な考慮事項5 ⚫ モデリングを意識したデータ整備 ✓ Power BIはストレージモード (Import, DirectQuery, Direct Lake, その他) に関係なく、 スタースキーマとなるよう、データを整備すること

• https://learn.microsoft.com/power-bi/guidance/star-schema

Power Queryでデータを扱う際の重要な考慮事項5.1 ⚫ モデリングのコツ ✓ モデリングを正しく行うため、ソースデータ（テーブル）の中身を知る ✓ M関数のTable.Schema / Table.Profileを活用し、メタデータ情報を取得

• https://marshal115.hatenablog.com/entry/2022/02/17/001701#メタデータ情報の確認

Power Queryでデータを扱う際の重要な考慮事項6 ⚫ クラウド版 (DFG1, DFG2) vs Desktop版 (Power BI, Excel) の操作相違 ✓ クラウド版はUIを使って、Binaryの状態ではファイルを解析し、展開できない ✓ Power BI Desktop / Excel はダブルクリック or 右クリックから解析・展開できる クリックしても効かない

Power Queryでデータを扱う際の重要な考慮事項6.1 ⚫ クラウド版 (DFG1, DFG2) vs Desktop版 (Power BI, Excel) の操作相違 ✓ クラウド版はオートコンプリート機能が効かない ✓ M言語を知らないと操作に困る = 初心者はUI操作に頼ることになる 躓きそうになったら、 Power BI Desktopで 同じ操作をやってみる データを出力する必要 がある場合、Excel Power Queryを使う

Power Queryでデータを扱う際の重要な考慮事項6.2 ⚫ ①「読み込みを有効」 vs ②「ステージングを有効」 ✓ ①について、PBI DesktopとDFG1は同じ仕様、DFG2は②しか存在しない ✓ ステージングはQuery Foldingをサポートしていないソースがある場合等に活用すると良い Data Factory スポットライト: Dataflow Gen2 |Microsoft Fabric ブログ |マイクロソフトファブリック

• https://learn.microsoft.com/power-query/query-folding-basics
• https://blog.fabric.microsoft.com/en-us/blog/data-factory-spotlight-dataflows-gen2/

Power Queryでデータを扱う際の重要な考慮事項7 ⚫ 常に数字が正しいかどうかを確認 ✓ Left Outer Joinを行った際、行数が増えていないか？

Power Queryでデータを扱う際の重要な考慮事項7.1 ⚫ 行数が多い場合の確認方法は？ ✓ 行数のカウント (Table.RowCount) を使用

• https://learn.microsoft.com/powerquery-m/table-rowcount

Power Queryでデータを扱う際の重要な考慮事項8 ⚫ 行 (レコード) と列 (リスト) の選択 ✓ A) 1行目の選択: 前ステップ名{0} ✓ B) 特定の列の選択: 前ステップ名[列名] or 列選択 → 右クリック > ドリルダウン

Power Queryでデータを扱う際の重要な考慮事項9 ⚫ 列をスマートに選択する ✓ Ctrlを押しながら列を選択する ✓ 選択した順番に「他の列の削除」 or を行うと、その順番通りに並び替えられる ✓ 列の入れ替えは行わない = 無駄なステップ (Excelとは違う) ✓ 効率良く列を選択する方法を学ぶ Power BIで列を効果的に選択する方法

• https://marshal115.hatenablog.com/entry/2023/04/07/194959

Power Queryでデータを扱う際の重要な考慮事項10 ⚫ その他ベストプラクティス ✓ 可能な限り、クエリフォールディングを実現する (読み込み速度が圧倒的に良くなる) ✓ リソースヘビーな処理に気を付ける ✓ 行の並び替え (Table.Sort) → 基本的に行わない ✓ グループ化 (Table.Group) →データ量が大きい場合、重くなる ✓ マージ (Table.NestedJoin) →同上 ✓ プロファイル (Table.Profile) → 同上 ✓ 書式設定は必ず全ての列に対して行う。可能な限り、「適用されたステップ」の最後で行う ✓ Power Queryパラメータを活用 ✓ Power Query Template (PQT) の活用 ✓ PQTへのエクスポート: Excel, DFG1, DFG2が可能 ✓ PQTへのインポート: DFG1, DFG2が可能

• https://learn.microsoft.com/power-query/query-folding-basics
• https://learn.microsoft.com/power-query/power-query-template

Microsoft Thank you!