Power Query_Fundamentals

8.4K Views

July 08, 24

スライド概要

Power Queryテクノロジーに関する基礎的な解説

シェア

またはPlayer版

埋め込む »CMSなどでJSが使えない場合

関連スライド

各ページのテキスト
1.

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

2.

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

3.

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

4.

Power Queryの概要

5.

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

6.

用語・ニュアンスの確認 ⚫ 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) が存在する(後述)

7.

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

8.

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

9.

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

10.

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

11.

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

12.

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

13.

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

14.

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

15.

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の解説

16.

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

17.

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

18.

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使用時

19.

Power Queryの設定

20.

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

21.

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

22.

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

23.

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

24.

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

25.

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

26.

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

27.

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

28.

初期設定① ⚫ 設定を開く

29.

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

30.

Power Queryデモ

31.

デモで行われる主な操作 ⚫ 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)

32.

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

33.

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

34.

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

35.

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