SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

Power BI Desktop チートシート

one comment

Power Query (informally known as "M") Formula reference

https://msdn.microsoft.com/en-us/library/mt211003.aspx
http://download.microsoft.com/download/8/1/A/81A62C9B-04D5-4B6D-B162-D28E4D848552/Power%20Query%20Formula%20Language%20Specification%20(August%202015).pdf

Power Query M function reference

https://msdn.microsoft.com/en-us/library/mt779182.aspx

Expressions, values, and let expression
https://msdn.microsoft.com/en-us/library/mt299038.aspx

Type conversion
https://msdn.microsoft.com/en-us/library/mt186370.aspx

Power BI Desktop でのデータ型
https://powerbi.microsoft.com/ja-jp/documentation/powerbi-desktop-data-types/

Data Analysis Expressions (DAX) リファレンス
https://msdn.microsoft.com/library/gg413422.aspx

視覚化

種類

備考

積み上げ横棒グラフ

 

積み上げ縦棒グラフ

 

集合横棒グラフ

 

集合縦棒グラフ

 

100 積み上げ横棒グラフ

 

100% 積み上げ縦棒具フラフ

 

折れ線グラフ

 

面グラフ

 

積み上げ面グラフ

 

折れ線グラフおよび積み上げ縦棒グラフ

 

折れ線グラフおよび集合縦棒グラフ

 

ウォーターフォール図

 

散布図

 

円グラフ

 

TreeMap

 

マップ

 

テーブル

 

マトリックス

 

塗り分け地図

 

フィルター

 

ゲージ

 

複数の行カード

 

カード

 

KPI

 

スライサー

 

ドーナツ グラフ

 

R ビジュアル

 

ファイルからインポートする

 

 

ホーム

列の管理

操作

クエリ

備考

列の選択

Table.SelectColumns(ソース,{"列1", "列2"})

 

列の削除

Table.RemoveColumns(ソース,{"列1"})

 

 

行の削減

操作

クエリ

備考

行の保持上位の行を保持

Table.FirstN(ソース,1)

 

行の保持下位の行を保持

Table.LastN(ソース, 1)

 

行の保持行の範囲の保持

Table.Range(ソース,1,3)

 

行の保持重複の保持

let

columnNames = {"列1"},

addCount = Table.Group(ソース, columnNames, {{"Count", Table.RowCount, type number}}),

selectDuplicates = Table.SelectRows(addCount, each [Count] > 1),

removeCount = Table.RemoveColumns(selectDuplicates, "Count")

in

Table.Join(ソース, columnNames, removeCount, columnNames, JoinKind.Inner)

 

行の保持エラーの保持

Table.SelectRowsWithErrors(ソース, {"列1"})

 

 

操作

クエリ

備考

行の削除上位の行の削除

Table.Skip(ソース,1)

 

行の削除下位の行の削除

Table.RemoveLastN(ソース,1)

 

行の削除代替行の削除

Table.AlternateRows(ソース,0,2,1)

 

行の削除重複の削除

Table.Distinct(ソース, {"列1"})

 

行の削除空白行の削除

Table.SelectRows(ソース, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))

 

行の削除エラーの削除

Table.RemoveRowsWithErrors(ソース, {"列1"})

 

 

並べ替え

操作

クエリ

備考

昇順で並べ替え

Table.Sort(ソース,{{"列1", Order.Ascending}})

 

降順で並べ替え

Table.Sort(ソース,{{"列1", Order.Descending}})

 

 

変換

操作

クエリ

備考

列の分割区切り記号による分割

Table.SplitColumn(Table.TransformColumnTypes(ソース, {{"列2", type text}}, "ja-JP"),"列2",Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv),{"列2.1", "列2.2", "列2.3"}),

 

列の分割文字数による分割

Table.SplitColumn(Table.TransformColumnTypes(ソース, {{"列2", type text}}, "ja-JP"),"列2",Splitter.SplitTextByRepeatedLengths(1),{"列2.1", "列2.2"}),

 

 

操作

クエリ

備考

グループ化

Table.Group(ソース, {"列1"}, {{"カウント", each Table.RowCount(_), type number}})

 

 

操作

クエリ

備考

データ型

Table.TransformColumnTypes(ソース,{{"列1", Int64.Type}, {"列2", type number}})

 

 

操作

クエリ

備考

先頭の行を見出しとして使用先頭の行を見出しとして使用

Table.PromoteHeaders(ソース, [PromoteAllScalars=true])

 

先頭の行を見出しとして使用ヘッダーを 1 行目として使う

Table.DemoteHeaders(ソース)

 

 

操作

クエリ

備考

値の置換

Table.ReplaceValue(ソース,"1","2",Replacer.ReplaceText,{"列1", "列2"})

 

 

 

結合

操作

クエリ

備考

クエリのマージクエリのマージ

Table.NestedJoin(ソース,{"列1"},T2,{"列1"},"NewColumn",JoinKind.RightOuter)

 

クエリのマージ新規としてクエリをマージ

Table.NestedJoin(T1,{"列1"},T2,{"列1"},"NewColumn",JoinKind.LeftOuter)

 

 

操作

クエリ

備考

マージ後のテーブルの展開

Table.ExpandTableColumn(マージされたクエリ数, "NewColumn", {"列1", "列2"}, {"NewColumn.列1", "NewColumn.列2"})

 

 

操作

クエリ

備考

クエリの追加クエリの追加

Table.Combine({ソース, T2})

 

クエリの追加クエリを新規クエリとして追加

Table.Combine({T1, T2})

 

 

操作

クエリ

備考

ファイルの結合

 

 

 

変換

テーブル

操作

クエリ

備考

グループ化

Table.Group(ソース, {"列1"}, {{"カウント", each Table.RowCount(_), type number}})

 

先頭の行を見出しとして使用

Table.PromoteHeaders(ソース, [PromoteAllScalars=true])

 

入れ替え

Table.Transpose(ソース)

 

行の反転

Table.ReverseRows(ソース)

 

行数のカウント

Table.RowCount(ソース)

 

 

任意の列

操作

クエリ

備考

データ型

Table.TransformColumnTypes(ソース,{{"列1", type text}, {"列2", type text}})

 

データ型の検出

Table.TransformColumnTypes(ソース,{{"列1", Int64.Type}, {"列2", type date}})

 

名前の変更

Table.RenameColumns(ソース,{{"列1", "C1"}, {"列2", "C2"}})

 

値の置換

Table.ReplaceValue(ソース,"1","2",Replacer.ReplaceText,{"列1", "列2"})

 

フィル

Table.FillDown(ソース,{"列2"})

 

フィル

Table.FillUp(ソース,{"列2"})

 

列のピボット

Table.Pivot(ソース, List.Distinct(ソース[列2]), "列2", "列1", List.Count)

 

列のピボット解除列のピボット解除

Table.UnpivotOtherColumns(ピボットされた列, {"2", "3", "4"}, "属性", "値")

 

列のピボット解除その他の列のピボット解除

Table.UnpivotOtherColumns(ピボットされた列, {"1"}, "属性", "値")

 

移動左へ移動

Table.ReorderColumns(ソース,{"列2", "列1"})

 

移動右へ移動

Table.ReorderColumns(ソース,{"列2", "列1"})

 

移動先頭に移動

Table.ReorderColumns(ソース,{"列2", "列1"})

 

移動末尾に移動

Table.ReorderColumns(ソース,{"列2", "列1"})

 

リストに変換する

ソース[列2]

 

 

テキストの列

操作

クエリ

備考

列の分割区切り記号による分割

Table.SplitColumn(Table.TransformColumnTypes(変更された型, {{"列2", type text}}, "ja-JP"),"列2",Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv),{"列2.1", "列2.2", "列2.3"}),

 

列の分割文字数による分割

Table.SplitColumn(Table.TransformColumnTypes(変更された型, {{"列2", type text}}, "ja-JP"),"列2",Splitter.SplitTextByRepeatedLengths(1),{"列2.1", "列2.2", "列2.3", "列2.4", "列2.5", "列2.6", "列2.7", "列2.8", "列2.9", "列2.10", "列2.11", "列2.12", "列2.13", "列2.14", "列2.15", "列2.16", "列2.17", "列2.18"}),

 

 

操作

クエリ

備考

書式小文字

Table.TransformColumns(ソース,{{"列1", Text.Lower}})

 

書式大文字

Table.TransformColumns(ソース,{{"列1", Text.Upper}})

 

書式各単語の先頭文字を大文字にする

Table.TransformColumns(ソース,{{"列1", Text.Proper}})

 

書式トリミング

Table.TransformColumns(ソース,{{"列1", Text.Trim}})

 

書式クリーン

Table.TransformColumns(ソース,{{"列1", Text.Clean}})

 

書式プレフィックスの追加

Table.TransformColumns(ソース, {{"列1", each "AAA" & _, type text}})

 

書式サフィックスの追加

Table.TransformColumns(ソース, {{"列1", each _ & "ZZZ", type text}})

 

 

操作

クエリ

備考

列のマージ

Table.CombineColumns(ソース,{"列1", "列2"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"結合済み")

 

 

操作

クエリ

備考

抽出長さ

Table.TransformColumns(ソース,{{"列2", Text.Length}})

 

抽出最初の文字

Table.TransformColumns(ソース, {{"列2", each Text.Start(Text.From(_, "ja-JP"), 5), type text}})

 

抽出最後の文字

Table.TransformColumns(ソース, {{"列2", each Text.End(_, 5), type text}})

 

抽出範囲

Table.TransformColumns(ソース, {{"列2", each Text.Middle(_, 2, 4), type text}})

 

 

操作

クエリ

備考

解析  – XML

Table.TransformColumns(ソース,{{"列2", Xml.Tables}})

 

解析 – JSON

Table.TransformColumns(ソース,{{"列2", Json.Document}})

 

 

数値の列

操作

クエリ

備考

統計合計

List.Sum(変更された型[列1])

 

統計最小値

List.Min(変更された型[列1])

 

統計最大値

List.Max(変更された型[列1])

 

統計中央値

List.Median(変更された型[列1])

 

統計平均

List.Average(変更された型[列1])

 

統計標準偏差

List.StandardDeviation(変更された型[列1])

 

統計値のカウント

List.NonNullCount(変更された型[列1])

 

統計個別の値のカウント

List.NonNullCount(List.Distinct(変更された型[列1]))

 

 

操作

クエリ

備考

標準加算

Table.TransformColumns(変更された型, {{"列1", each _ + 1, type number}})

 

標準乗算

Table.TransformColumns(変更された型, {{"列1", each _ * 2, type number}})

 

標準減算

Table.TransformColumns(変更された型, {{"列1", each _ – 2, type number}})

 

標準除算

Table.TransformColumns(変更された型, {{"列1", each _ / 2, type number}})

 

標準整数除算

Table.TransformColumns(変更された型, {{"列1", each Number.IntegerDivide(_, 3), Int64.Type}})

 

標準剰余

Table.TransformColumns(変更された型, {{"列1", each Number.Mod(_, 2), type number}})

 

標準パーセンテージ

Table.TransformColumns(変更された型, {{"列1", each _ * 4 / 100, type number}})

 

標準次に対するパーセンテージ

Table.TransformColumns(変更された型, {{"列1", each _ / 5 * 100, type number}})

 

 

操作

クエリ

備考

指数絶対値

Table.TransformColumns(変更された型,{{"列1", Number.Abs, type number}})

 

指数累乗 – 2

Table.TransformColumns(変更された型,{{"列1", each Number.Power(_, 2), type number}})

 

指数累乗 – 3

Table.TransformColumns(変更された型,{{"列1", each Number.Power(_, 3), type number}})

 

指数累乗累乗

Table.TransformColumns(変更された型,{{"列1", each Number.Power(_, 5), type number}})

 

指数平方根

Table.TransformColumns(変更された型,{{"列1", Number.Sqrt, type number}})

 

指数指数

Table.TransformColumns(変更された型,{{"列1", Number.Exp, type number}})

 

指数対数常用対数

Table.TransformColumns(変更された型,{{"列1", Number.Log10, type number}})

 

指数対数自然対数

Table.TransformColumns(変更された型,{{"列1", Number.Ln, type number}})

 

指数階乗

Table.TransformColumns(変更された型,{{"列1", Number.Factorial, type number}})

 

 

 

操作

クエリ

備考

三角関数サイン

Table.TransformColumns(変更された型,{{"列1", Number.Sin, type number}})

 

三角関数コサイン

Table.TransformColumns(変更された型,{{"列1", Number.Cos, type number}})

 

三角関数タンジェント

Table.TransformColumns(変更された型,{{"列1", Number.Tan, type number}})

 

三角関数アークサイン

Table.TransformColumns(変更された型,{{"列1", Number.Asin, type number}})

 

三角関数アークコサイン

Table.TransformColumns(変更された型,{{"列1", Number.Acos, type number}})

 

三角関数アークタンジェント

Table.TransformColumns(変更された型,{{"列1", Number.Atan, type number}})

 

 

操作

クエリ

備考

丸め切り上げ

Table.TransformColumns(変更された型,{{"列1", Number.RoundUp, Int64.Type}})

 

丸め切り捨て

Table.TransformColumns(変更された型,{{"列1", Number.RoundDown, Int64.Type}})

 

丸め四捨五入

Table.TransformColumns(変更された型,{{"列1", each Number.Round(_, 2), type number}})

 

 

操作

クエリ

備考

情報偶数

Table.TransformColumns(変更された型,{{"列1", Number.IsEven, type logical}})

 

情報奇数

Table.TransformColumns(変更された型,{{"列1", Number.IsOdd, type logical}})

 

情報符号

Table.TransformColumns(変更された型,{{"列1", Number.Sign, Int64.Type}})

 

 

 

日付と時刻の列

操作

クエリ

備考

日付期間

Table.TransformColumns(変更された型,{{"列2", each DateTime.LocalNow() – _, type duration}})

 

日付日付のみ

Table.TransformColumns(変更された型,{{"列2", DateTime.Date}})

 

日付解析

Table.TransformColumns(変更された型,{{"列1", each Date.From(DateTimeZone.From(_)), type date}})

 

日付

Table.TransformColumns(変更された型,{{"列2", Date.Year}})

 

日付年の開始日

Table.TransformColumns(変更された型,{{"列2", Date.StartOfYear, type datetime}})

 

日付年の最終日

Table.TransformColumns(変更された型,{{"列2", Date.EndOfYear, type datetime}})

 

日付

Table.TransformColumns(変更された型,{{"列2", Date.Month}})

 

日付月の開始日

Table.TransformColumns(変更された型,{{"列2", Date.StartOfMonth, type datetime}})

 

日付月の最終日

Table.TransformColumns(変更された型,{{"列2", Date.EndOfMonth, type datetime}})

 

日付月内の日数

Table.TransformColumns(変更された型,{{"列2", Date.DaysInMonth}})

 

日付月の名前

Table.TransformColumns(変更された型, {{"列2", each Date.MonthName(_), type text}})

 

日付四半期年の四半期

Table.TransformColumns(変更された型,{{"列2", Date.QuarterOfYear}})

 

日付四半期四半期の開始日

Table.TransformColumns(変更された型,{{"列2", Date.StartOfQuarter, type datetime}})

 

日付四半期四半期の終了日

Table.TransformColumns(変更された型,{{"列2", Date.EndOfQuarter, type datetime}})

 

日付年の通算週

Table.TransformColumns(変更された型,{{"列2", Date.WeekOfYear}})

 

日付月の通算週

Table.TransformColumns(変更された型,{{"列2", Date.WeekOfMonth}})

 

日付週の開始日

Table.TransformColumns(変更された型,{{"列2", Date.StartOfWeek, type datetime}})

 

日付週の終了日

Table.TransformColumns(変更された型,{{"列2", Date.EndOfWeek, type datetime}})

 

日付

Table.TransformColumns(変更された型,{{"列2", Date.Day}})

 

日付週の通算日

Table.TransformColumns(変更された型,{{"列2", Date.DayOfWeek}})

 

日付年の通算日

Table.TransformColumns(変更された型,{{"列2", Date.DayOfYear}})

 

日付一日の開始時刻

Table.TransformColumns(変更された型,{{"列2", Date.StartOfDay}})

 

日付最終日

Table.TransformColumns(変更された型,{{"列2", Date.EndOfDay}})

 

日付曜日名

Table.TransformColumns(変更された型, {{"列2", each Date.DayOfWeekName(_), type text}})

 

日付日数の減算

Table.AddColumn(追加されたカスタム1, "DateDifference", each Duration.Days([カスタム.1] – [列2]), Int64.Type)

 

日付日付と時刻の組み合わせ

Table.AddColumn(削除された列, "結合済み", each [カスタム] & [Date], type datetime)

 

日付最も早い

List.Min(変更された型[列2])

 

日付最も遅い

List.Max(変更された型[列2])

 

 

操作

クエリ

備考

時刻時刻のみ

Table.TransformColumns(計算された現地時刻,{{"列2", DateTime.Time}})

 

時刻現地時刻

Table.TransformColumns(変更された型,{{"列2", DateTimeZone.ToLocal, type datetimezone}})

 

時刻解析

Table.TransformColumns(計算された現地時刻,{{"列1", each Time.From(DateTimeZone.From(_)), type time}})

 

時刻

Table.TransformColumns(抽出された時刻,{{"列2", Time.Hour}})

 

時刻時間の始まり

Table.TransformColumns(抽出された時刻,{{"列2", Time.StartOfHour, type time}})

 

時刻時間の終わり

Table.TransformColumns(抽出された時刻,{{"列2", Time.EndOfHour, type time}})

 

時刻

Table.TransformColumns(抽出された時刻,{{"列2", Time.Minute}})

 

時刻

Table.TransformColumns(抽出された時刻,{{"列2", Time.Second}})

 

時刻日付と時刻の組み合わせ

Table.CombineColumns(変更された型1, {"列2", "カスタム"}, (columns) => List.First(columns) & List.Last(columns), "結合済み")

 

時刻最も早い

List.Min(抽出された時刻[列2])

 

時刻最も遅い

List.Max(抽出された時刻[列2])

 

 

操作

クエリ

備考

期間日間

Table.TransformColumns(変更された型,{{"列1", Duration.Days, Int64.Type}})

 

期間時間

Table.TransformColumns(変更された型,{{"列1", Duration.Hours, Int64.Type}})

 

期間

Table.TransformColumns(変更された型,{{"列1", Duration.Minutes, Int64.Type}})

 

期間

Table.TransformColumns(変更された型,{{"列1", Duration.Seconds, type number}})

 

期間合計年数

Table.TransformColumns(変更された型,{{"列1", each Duration.TotalDays(_) / 365, type number}})

 

期間合計日数

Table.TransformColumns(変更された型,{{"列1", Duration.TotalDays, type number}})

 

期間合計時間数

Table.TransformColumns(変更された型,{{"列1", Duration.TotalHours, type number}})

 

期間合計分数

Table.TransformColumns(変更された型,{{"列1", Duration.TotalMinutes, type number}})

 

期間合計秒数

Table.TransformColumns(変更された型,{{"列1", Duration.TotalMinutes, type number}})

 

期間乗算

Table.TransformColumns(変更された型, {{"列1", each _ * 2, type duration}})

 

期間除算

Table.TransformColumns(変更された型, {{"列1", each _ / 3, type duration}})

 

期間統計合計

List.Sum(変更された型[列1])

 

期間統計最小値

List.Min(変更された型[列1])

 

期間統計最大値

List.Max(変更された型[列1])

 

期間統計中央値

List.Median(変更された型[列1])

 

期間統計平均

List.Average(変更された型[列1])

 

 

 

 

リストツール

※リストを選択している場合のみ使用可能 / リストはテーブルとは異なり「変換」「列の追加」タブの操作は使用できない

変換

操作

クエリ

備考

テーブルへの変換

Table.FromList(列1, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

 

 

アイテムの管理

操作

クエリ

備考

アイテムの保持上位のアイテムの保持

List.FirstN(列1,f1)

 

アイテムの保持下位のアイテムの保持

List.LastN(列1, 1)

 

アイテムの保持アイテムの範囲の保持

List.Range(列1,1,4)

 

アイテムの削除上位のアイテムの削除

List.Skip(列1,1)

 

アイテムの削除下位のアイテムの削除

List.RemoveLastN(列1,1)

 

アイテムの削除代替アイテムの削除

List.Alternate(列1,3,2,0)

 

重複の削除

List.Distinct(列1)

 

アイテムの反転

List.Reverse(列1)

 

 

並べ替え

操作

クエリ

備考

昇順で並べ替え

List.Sort(列1,Order.Ascending)

 

降順で並べ替え

List.Sort(列1,Order.Descending)

 

 

数値リスト

操作

クエリ

備考

統計値のカウント

List.NonNullCount(列1)

 

統計個別の値のカウント

List.NonNullCount(List.Distinct(列1))

 

Written by masayuki.ozawa

3月 19th, 2017 at 10:08 pm

Posted in Power BI

Tagged with

One Response to 'Power BI Desktop チートシート'

Subscribe to comments with RSS or TrackBack to 'Power BI Desktop チートシート'.

  1. […] Power BI Desktop チートシート […]

Leave a Reply

*