Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Pro : Microsoft 365)
Power Query データを条件テーブルの列名と値でフィルタする
投稿日時: 24/01/31 23:19:31
投稿者: tarima

Power Queryで以下を実現したいです。Power Queryは日常的に使用しています。
データを条件テーブルの列名と値でフィルタする
どのようなステップを書いたらよいかアドバイスを頂けないでしょうか。
 
フィルタ対象列は不定です。
データテーブルを条件テーブルの(列名1=条件1)or(列名2=条件2)...でフィルタ
(複数列をOR条件でフィルタ)
 
簡単な例で、シートのFILTER関数でいうと以下です。
=FILTER(tblData3,(tblData3[店舗]="店舗A")+(tblData3[商品]="商品111"))
 
実データは、列数が30以上あります。条件テーブルの条件に値がないものはフィルタ対象外
 

データテーブル                
id    カテゴリ    店舗    商品    個数
1    ABC    店舗A    商品111    500
2    ABC    店舗B    商品222    1000
3    CDE    店舗B    商品333    600
4    CDE    店舗E    商品111    700
5    ABC    店舗E    商品222    900
6    CDE    店舗A    商品666    400
7    FGH    店舗C    商品111    200
8    FGH    店舗A    商品888    300
9    ABC    店舗D    商品999    700
10    CDE    店舗B    商品111    800
 
 
条件テーブル    
列名    条件
カテゴリ    
店舗    店舗A
商品    商品111
 
 
結果
1    ABC    店舗A    商品111    500
4    CDE    店舗E    商品111    700
6    CDE    店舗A    商品666    400
7    FGH    店舗C    商品111    200
8    FGH    店舗A    商品888    300
10    CDE    店舗B    商品111    800

回答
投稿日時: 24/02/01 10:42:29
投稿者: sk

引用:
フィルタ対象列は不定です。

引用:
複数列をOR条件でフィルタ

引用:
条件テーブルの条件に値がないものはフィルタ対象外

とりあえず思いついたのは次のようなクエリ。
 
(詳細エディター)
-----------------------------------------------------------
let
    GetTable = (SourceTable as table, ColumnName as text, ConditionValue as any) =>
        let
            FilteredRows = Table.SelectRows(SourceTable, each (Table.Column(_, ColumnName) = ConditionValue))
        in
            FilteredRows,
    DataTable = Excel.CurrentWorkbook(){[Name="データテーブル"]}[Content],
    ChangedTypeOfDataTable = Table.TransformColumnTypes(DataTable,{{"id", Int64.Type}, {"カテゴリ", type text}, {"店舗", type text}, {"商品", type text}, {"個数", Int64.Type}}),
    ConditionTable = Excel.CurrentWorkbook(){[Name="条件テーブル"]}[Content],
    ChangedTypeOfConditionTable = Table.TransformColumnTypes(ConditionTable,{{"列名", type text}}),
    PickedConditions = Table.SelectRows(ChangedTypeOfConditionTable, each ([列名] <> null and [条件] <> null)),
    GetFilteredTables = Table.AddColumn(PickedConditions, "抽出結果", each GetTable(ChangedTypeOfDataTable, [列名], [条件])),
    Expanded = Table.ExpandTableColumn(GetFilteredTables, "抽出結果", {"id", "カテゴリ", "店舗", "商品", "個数"}, {"id", "カテゴリ", "店舗", "商品", "個数"}),
    RemovedColumns = Table.RemoveColumns(Expanded,{"列名", "条件"}),
    RemovedDuplicates = Table.Distinct(RemovedColumns),
    SortedRows = Table.Sort(RemovedDuplicates,{{"id", Order.Ascending}})
in
    SortedRows
-----------------------------------------------------------

回答
投稿日時: 24/02/01 10:47:57
投稿者: gombohori

 このような感じではいかがでしょうか
 
クエリ 条件テーブル

 let
    ソース = Excel.CurrentWorkbook(){[Name="条件テーブル"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"列名", type text}, {"条件", type text}}),
    フィルターされた行 = Table.SelectRows(変更された型, each [条件] <> null and [条件] <> "") //条件列が空白の場合は除外
 in
    フィルターされた行


 
クエリ データテーブル
 let
    ソース = Excel.CurrentWorkbook(){[Name="データテーブル"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"id", Int64.Type}, {"カテゴリ", type text}, {"店舗", type text}, {"商品", type text}, {"個数", Int64.Type}}),
    フィルターされた行 = Table.SelectRows(ソース,each Record.SelectFields(_,条件テーブル[列名])=Record.FromList(条件テーブル[条件],条件テーブル[列名]))
 in
    フィルターされた行

回答
投稿日時: 24/02/01 12:56:54
投稿者: gombohori

 ごめんなさい
 上記のクエリはAND条件になってました

回答
投稿日時: 24/02/01 13:42:29
投稿者: んなっと

データ量が多いと重いけど、こんなのも。
 
let
    ソース = Excel.CurrentWorkbook(){[Name="データテーブル"]}[Content],
    Crt = Excel.CurrentWorkbook(){[Name="条件テーブル"]}[Content],
    Crt1 = Table.SelectRows(Crt, each ([条件] <> null)),
    Flt = Table.SelectRows(ソース,each List.Accumulate({0..Table.RowCount(Crt1)-1},false,
        (s,i) => s or Record.Field(_, Crt1[列名]{i})=Crt1[条件]{i}))
in
    Flt

投稿日時: 24/02/01 15:38:31
投稿者: tarima

んなっと様、さすが、すごいです!完璧に希望どおり実現できました。
自分では何年かかってもできそうにないです。
こんなにシンプルできれいなコード!いつもありがとうございます。
 
 

んなっと さんの引用:
データ量が多いと重いけど、こんなのも。
 
let
    ソース = Excel.CurrentWorkbook(){[Name="データテーブル"]}[Content],
    Crt = Excel.CurrentWorkbook(){[Name="条件テーブル"]}[Content],
    Crt1 = Table.SelectRows(Crt, each ([条件] <> null)),
    Flt = Table.SelectRows(ソース,each List.Accumulate({0..Table.RowCount(Crt1)-1},false,
        (s,i) => s or Record.Field(_, Crt1[列名]{i})=Crt1[条件]{i}))
in
    Flt