Excel中Power Query的Table.SelectRows应用：维度表与事实表处理需求

Hey there! Let's walk through how to use Power Query's Table.SelectRows function to handle your two Excel tables, based on the matching GroupName and Description fields.

First, let's recap your table structures and data for clarity:

• Table1 (Group Dimension Table)
  Fields: GroupName, Location, GroupID
  Data rows:

  • Even Steven | New York | 04952
  • BillyBobs | Texas | 04035
  • Nunya | California | 04859

• Table2 (Fact Table)
  Fields: GroupID, Description, SalesVolume
  Data rows:

  • 04952 | Even Steven | 59003
  • 04035 | BillyBobs | 98390
  • 04859 | Nunya | 50194

Since GroupName and Description contain identical values, we can leverage this match with Table.SelectRows for common filtering tasks. Below are two typical scenarios:

Scenario 1: Filter Table2 to keep only groups present in Table1 #

If you want to retain only rows in Table2 that have a matching group in Table1 (using the text fields instead of GroupID), use this M code in Power Query:

let
    // Load Table1 and extract the list of GroupNames
    Source_Table1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    GroupNames = Source_Table1[GroupName],
    
    // Load Table2 and filter rows where Description matches a GroupName
    Source_Table2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Filtered_Table2 = Table.SelectRows(Source_Table2, each List.Contains(GroupNames, [Description]))
in
    Filtered_Table2

Scenario 2: Filter Table1 to keep only groups with sales data in Table2 #

If you want to filter Table1 to show only groups that have corresponding sales records in Table2, use this variation:

let
    // Load Table2 and extract the list of Descriptions
    Source_Table2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Descriptions = Source_Table2[Description],
    
    // Load Table1 and filter rows where GroupName matches a Description
    Source_Table1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Filtered_Table1 = Table.SelectRows(Source_Table1, each List.Contains(Descriptions, [GroupName]))
in
    Filtered_Table1

Quick Notes #

• We use List.Contains inside Table.SelectRows to check if the text field from one table exists in the matching list from the other table.
• While GroupID is a more reliable key for joining/filtering, these solutions strictly follow your request to use the matching text fields with Table.SelectRows.

内容的提问来源于stack exchange，提问作者SUMguy