Q&A
実現したいこと
PowerBIのクエリで複数要素が該当するかどうかを実現したい。
１つのExcelファイルの特定の列に関して該当する要素があるかどうかの判定方法は以前教えて頂きましたが、今回は
C:\Users\0000910170\OneDrive - XXX\SmartAccessMecha\Smart_Access_Data\アクセスデータ"
というフォルダの下に複数のフォルダがあり、そのフォルダの中に複数のcsvファイルがあります。
これらのcsvファイルをPowerBIで取り込み、JSONの処理をして必要な列のあるテーブルを作成し、式は以下のようになっています。
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
let
ソース = Folder.Files("C:\Users\XXXX\OneDrive\SmartAccessMecha\Smart_Access_Data\アクセスデータ"),
#"フィルター選択された非表示の File1" = Table.SelectRows(ソース, each [Attributes]?[Hidden]? <> true),
カスタム関数の呼び出し1 = Table.AddColumn(#"フィルター選択された非表示の File1", "ファイルの変換", each ファイルの変換([Content])),
#"名前が変更された列 1" = Table.RenameColumns(カスタム関数の呼び出し1, {"Name", "Source.Name"}),
削除された他の列1 = Table.SelectColumns(#"名前が変更された列 1", {"Source.Name", "ファイルの変換"}),
展開されたテーブル列1 = Table.ExpandTableColumn(削除された他の列1, "ファイルの変換", Table.ColumnNames(ファイルの変換(#"サンプル ファイル"))),
変更された型 = Table.TransformColumnTypes(展開されたテーブル列1,{{"Source.Name", type text}, {"RecordId", type text}, {"CreationDate", type datetime}, {"RecordType", Int64.Type}, {"Operation", type text}, {"UserId", type text}, {"AuditData", type text}, {"AssociatedAdminUnits", type text}}),
フィルターされた行 = Table.SelectRows(変更された型, each ([RecordId] <> "]""")),
#"解析された JSON" = Table.TransformColumns(フィルターされた行,{{"AuditData", Json.Document}}),
#"展開された AuditData" = Table.ExpandRecordColumn(#"解析された JSON", "AuditData", {"ObjectId", "SourceFileExtension", "SourceFileName"}, {"AuditData.ObjectId", "AuditData.SourceFileExtension", "AuditData.SourceFileName"}),
フィルターされた行1 = Table.SelectRows(#"展開された AuditData", each true)
in
フィルターされた行1
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
＜実現したいこと＞
"AuditData.ObjectId"の行ににある要素に関して除外ワードリストというテーブルにある除外ワードの列の要素を含むか含まないかを判定する列を作りたいと考えています。
前提
前回教えてもらった１つのExcelファイルの特定の列に関して該当する要素があるかどうかの判定方法は以下のようになっています。
前回の場合は"AuditData.ObjectId"ではなく、"コンテンツの場所"で判定しています。
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
let
//除外ワードを含むか否かを判定する関数を作る
IsExcluded = (Expression as text) =>
let
//テーブル[除外ワードリスト]の[除外ワード]列に、引数 Expression に渡されたテキストと部分一致する要素が存在する場合は true を、存在しない場合は false を返す
Judgement = List.MatchesAny(除外ワードリスト[除外ワード], each Text.Contains(Expression,_))
in
Judgement,
//ワークブックの参照
ソース = Excel.Workbook(File.Contents("C:\Users\XXXX\OneDrive\My_Data\アクセス履歴\Test\新しいフォルダー\全ユーザー結果_01.xlsm"), null, true),
//ワークシートの参照
データ_Sheet = ソース{[Item="データ",Kind="Sheet"]}[Data],
//1行目をヘッダー行として使用
昇格されたヘッダー数 = Table.PromoteHeaders(データ_Sheet, [PromoteAllScalars=true]),
//各列のデータ型を変換
変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"アクセス日時", type datetime}, {"ユーザー", type text}, {"コンテンツの場所", type text}}),
//それぞれのレコードの[コンテンツの場所]の値を上記の IsExcluded 関数によって評価した結果を新たなカスタム列として追加する
追加されたカスタム = Table.AddColumn(変更された型, "いずれかの除外ワードを含んでいる", each IsExcluded([コンテンツの場所]))
in
追加されたカスタム
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
元のデータが単独のExcelか複数のcsvファイルの違いによりソースの定義が異なること、教えてもらった式をcsvファイルのデータを読み込んでJSON処理したテーブルの式に単純に追加できないことが分かり困っています。
解決する式を教えて頂けると幸いです。よろしくお願いいたします。
回答1件
0
カスタム関数の作成手順
-
空のクエリを新規作成する。
-
そのクエリの名前を
IsContainedAnyInTextに変更する。 -
詳細エディターを開き、以下の式を記述する。
PowerQuery
1(Expression as text, Keys as list) => 2 let 3 //引数 Keys に渡されたリスト上に、引数 Expression に渡されたテキストの中に含まれている要素が1つ以上存在する場合は true を、存在しない場合は false を返す 4 Result = List.MatchesAny(Keys, each Text.Contains(Expression, _)) 5 in 6 Result
編集対象となるクエリ
最後のフィルターされた行1は完全に無駄なステップであるので、そのステップを削除した状態にする。
PowerQuery
1let 2 ソース = Folder.Files("C:\Users\XXXX\OneDrive\SmartAccessMecha\Smart_Access_Data\アクセスデータ"), 3 #"フィルター選択された非表示の File1" = Table.SelectRows(ソース, each [Attributes]?[Hidden]? <> true), 4 カスタム関数の呼び出し1 = Table.AddColumn(#"フィルター選択された非表示の File1", "ファイルの変換", each ファイルの変換([Content])), 5 #"名前が変更された列 1" = Table.RenameColumns(カスタム関数の呼び出し1, {"Name", "Source.Name"}), 6 削除された他の列1 = Table.SelectColumns(#"名前が変更された列 1", {"Source.Name", "ファイルの変換"}), 7 展開されたテーブル列1 = Table.ExpandTableColumn(削除された他の列1, "ファイルの変換", Table.ColumnNames(ファイルの変換(#"サンプル ファイル"))), 8 変更された型 = Table.TransformColumnTypes(展開されたテーブル列1,{{"Source.Name", type text}, {"RecordId", type text}, {"CreationDate", type datetime}, {"RecordType", Int64.Type}, {"Operation", type text}, {"UserId", type text}, {"AuditData", type text}, {"AssociatedAdminUnits", type text}}), 9 フィルターされた行 = Table.SelectRows(変更された型, each ([RecordId] <> "]""")), 10 #"解析された JSON" = Table.TransformColumns(フィルターされた行,{{"AuditData", Json.Document}}), 11 #"展開された AuditData" = Table.ExpandRecordColumn(#"解析された JSON", "AuditData", {"ObjectId", "SourceFileExtension", "SourceFileName"}, {"AuditData.ObjectId", "AuditData.SourceFileExtension", "AuditData.SourceFileName"}) 12in 13 #"展開された AuditData"
カスタム列の追加（カスタム関数の呼び出し）
"AuditData.ObjectId"の列
行ににある要素に関して除外ワードリストというテーブルにある除外ワードの列の要素を含むか含まないかを判定する列を作りたい
-
上記のクエリのステップ
展開された AuditDataが選択されている状態で、[列の追加]タブ → [全般]グループ → [カスタム関数の呼び出し]をクリックする。 -
表示された[カスタム関数の呼び出し]ダイアログの[新しい列名]に任意の名前を入力する。
-
[関数クエリ]ドロップダウンボックスより「IsContainedAnyInText」を選択する。
-
引数
Expressionに渡す列として「AuditData.ObjectId」を選択する。 -
引数
Keysに渡すリストを選択するため、[列の選択]ボタンをクリックする。 -
表示された[列の選択]ダイアログ上のドロップダウンボックスより「除外ワードリスト」を選択する。
-
選択されたクエリのプレビュー上の[除外ワード]列をクリックする。
-
[除外ワード]列が選択された状態で[OK]ボタンをクリックし、[列の選択]ダイアログを閉じる。
-
[カスタム関数の呼び出し]ダイアログ上において引数
Keysの指定が行われたことを確認し、[OK]ボタンをクリックする。
もし上記の操作をうまく行なえないのであれば、手順 1 において[カスタム関数の呼び出し]ではなく[カスタム列]をクリックし、表示された[カスタム列]ダイアログ上の[カスタム列の式]に以下の式を記述して下さい。
PowerQuery
1IsContainedAnyInText([AuditData.ObjectId], 除外ワードリスト[除外ワード])
投稿2024/07/02 07:40
編集2024/07/03 05:23
総合スコア938
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2024/07/02 07:55
2024/07/02 23:33
2024/07/03 06:02