Power Queryについて データソース（Extract) データソースからの取り込み Webから フォルダーから クエリのマージ 加工（Transform) テキスト変換 日付関数 フィル ピボット解除 Power Query以外のテクニック Power Queryについて modernexcel7.hatenablog.…

PowerShellの起動と初期設定 実行ポリシーの変更 PowerShell ISEによるスクリプトの実行テスト PowerShellでExcelを開く クエリの「バックグラウンドで更新する」をオフにする Excelを表示しながら実行 Excelを表示せずにバックグラウンドで実行 PowerShell…

今回のテーマはPowerQueryを使った住所に関する便利な小ネタです。 都道府県分割 都道府県の重複記述クレンジング 都道府県分割 例えば、以下のように都道府県と住所が一つにまとまったデータがあるとします。 これを都道府県とそれ以降の住所に分割します。…

List型を使った行の動的選択 今回のテーマはList型を使った行の動的選択です。前回のList型を使った列の動的選択では横軸である列を選択しましたが、今回は縦軸の行がテーマとなります。 modernexcel7.hatenablog.com 技術的なポイント 行選択リストの作成 L…

List型を使った列の動的選択 今日のテーマはList型のデータを用いた列の動的列選択です。PowerQueryでは、列の削除を行うことで、データソースから読み込んだデータのうち必要な列だけを残すことができます。 今回のゴールは、元表として以下のような５つの…

目標 技術的なポイント 実践 比較する二つのテーブルのマージ ピボット解除＆列のピボットで表の組み替え 変更の有り無しチェック 関数化してエクスポート＆共有 クエリ関数化 クエリのエクスポート クエリのインポート ソースコード 今回のソースコード 汎…

期間型データのDuration関数のサンプル式と結果の一覧を用意しました。適宜ご利用ください。 基本形 抽出形 数字で計算系 型変換系 関連記事 基本形 働き サンプル式 結果 期間型 #duration(1,2,3,4) 1.02:03:04 抽出形 働き サンプル式 結果 日数をとりだす…

テキスト型データのText関数のサンプル式と結果の一覧を用意しました。適宜ご利用ください。 型変換 部分抽出 置き換え 情報・検索・判定 加工 定数 関連記事 型変換 働き サンプル式 結果 文字コード⇒文字 Character.FromNumber(65) A 文字⇒文字コード Char…

今回は日付型データの関数のサンプル式と結果の一覧を用意しました。 また、それぞれの関数の特徴を元にカテゴライズしています。 適宜ご利用ください。 情報系 変換系 計算系 数える系 最初の日 最後の日 判定系 翌判定 翌X内判定 前判定 前X内判定 そのほ…

今回はパワークエリのデータ型のうち、日付型データの計算を扱います。 以下、前回の記事のサンプルデータを使用します。modernexcel7.hatenablog.comポイントとしては、日付・時刻の加減算には期間(Duration)型を使用する点です。 また、2点の日付・時間と…

今回はパワークエリの日付型データがテーマです。 それぞれ計算式のサンプルを載せますので実際に手を動かして試してみてください。 デモ用テーブルの用意 日付・時刻型 日付（date） 時刻（time） 日付/時刻（datetime） 日付/時刻/タイムゾーン（datetimez…

ピボット解除応用：多階層クロス集計表のデータ化 パワークエリのテクニックで人気の高いピボット解除ですが、今回は上に多階層乗ったクロス集計表の解体＝データ化がテーマです。ヘッダー部分が１階層（一行）であった場合はすんなりとピボット解除できるの…

[DAX] 第７章 予算vs実績比較 - 予算バージョン比較 [DAX] 第７章 予算vs実績比較 - 予算バージョン比較 目標 とりこむステップ ならべるステップ かぞえるステップ 選択されたスライサーから最大値・最小値を持ってくる 選択されたスライサーの最大値・最小…

計算・統計の使い方 計算・統計の使い方 Before After 実践 1. テーブルの読み込みと各商品の売上の計算 2. 個々の商品の売上の占める割合を出す 今回のテーマは「計算・統計の使い方とそれに関連したステップの過去参照」です。目標として以下のようなテー…

WEBサイトからデータを取り込む 今回は、WEBサイト上に掲載されているテーブル形式データを取り込みます。サンプルデータとしてこのサイトの以下テーブルを取り込みます。【サンプルデータ】 連番 テキスト 数字 1 あ 15 2 い 20 3 う 25 4 え 30 5 お 35 実…

「自作カレンダー」について 実践 空のクエリを開く 日付リストを作る カレンダーテーブルを作る 「自作カレンダー」について 通常データを読み込むために使うPower Queryですが、今回は逆にPower Queryでデータを用意します。サンプルとして自作のカレンダ…

「フィル」とは？ Before After 実践 例１：縦（空白セル） 例２：縦（セルの結合） 例３：横（セルの結合） 「フィル」とは？ 今回のテーマはフィルです。フィルとは、部分的にデータが存在する場合、それを縦方向（上または下）に埋める機能です。EXCELで…

「ピボット解除」とは？ 例１：横に入力された予算データを縦に並べ、数字テーブル（Fact Table）化する サンプルデータ 手順 三つのピボット解除について 「列のピボット解除」と「その他の列のピボット解除」 「選択した列のみをピボット解除」 例２：隣に…

データソースとは何か？ データソースの種類 ファイルから ブックから CSVから XMLから テキストから フォルダーから データベースから オンラインサービスから Facebookから その他のデータソースから Webから ODataフィードから ODBCから 空のクエリ テー…

皆さんはPower Queryをご存知でしょうか？ Power Queryは、Excelファイル、CSVファイル、テキストファイル、Access, SQL Server, ORACLE, Exchange, Salesforceなど多種多様なデータソースからのデータの取り込み、変換を自動化する機能です。 Excel2016以降…

テーマ 今回のテーマは、フォルダーの中の複数のExcelファイルをまとめて取り込む手順です。ポイントは以下の2点です。 Excel.Workbook関数の使用 データの取得は、フォルダー→シート→データの３段階 手順 データソースの指定 まずはフォルダーを読み込みま…

テーマ 今回は、前月と今月の差分を比べるような履歴の比較です。項目としてレコードの増減、数値、テキストを比較します。技術面のポイントは以下3点です。 1. 比較するための照合列（キー項目）を特定する 2. 結合の種類を完全外部で結合（マージ）し、統…

テーマ クエリの「マージ（結合）」の「結合の種類」について紹介します。 サンプルデータ 今回使うサンプルデータは以下の通りです。 左ファイル番号３が欠番です。 右ファイル番号４が欠番です。 結合の種類 「結合の種類」の選択により、「照合列」をキー…

テーマ マージとは、２つのデータを結合することです。 Power Queryに出てくるマージには列のマージとクエリのマージがありますが、本ページではクエリのマージを紹介します。 このクエリのマージを使えば、Excelで最も有名な関数、VLOOKUP関数を使用せずに…

テーマ 今回のテーマはフォルダーの中の複数のCSVファイルをまとめて取り込む手順です。技術的なポイントは以下の2点です。 Csv.Document関数の使用 文字コード（Encoding）の指定 動画 フォルダー内のCSV一括とりこみ 手順 データソースの指定 まずはフォル…