Filter using power query

Hi SamWhite 

Here is my solution. Hope it will be helpful!

The current table format is not friendly for Power Query or reporting, we need to transform it into a flat table. I guess all product data are in columns before "Market Segment" column and all Customer data are in columns after "Market Segment", so my main idea is to first split the original table into two tables ProductTable and CustomerTable, then transform them separately and filter only rows with "Z" value, finally merge two tables based on "Market Segment" column.  

Here is the full M code to realize my ideas. I have added some comments in it to explain what steps are doing. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKTylNLlHSUQpJzUktyyzOzM8DcgIy8vNSgbRPYkFJfgFINjEpJ7WkGMgCIufS4pL83NQiQyS2ERLbGIltgsQ2VYrViQYZEAUxB0o55+cCJTOTE3MgfKgMWBaqAaEYrD6vuKQI6GqIWxESWDWAKN/MvMy8dAzhKCTVSJaCzC8FukgBGjjFmJbEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, #"Market Segment" = _t, Customers = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Market Segment", type text}, {"Customers", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}}),
    
    // Get all column names        
    ColumnNames = Table.ColumnNames(#"Changed Type"),
    
    // find the position of "Market Segment" in column names
    SplitPosition = List.PositionOf(ColumnNames, "Market Segment"),
    
    // Select all columns before "Market Segment" (including "Market Segment" column) into a new table ProductTable
    // then transform the new ProductTable with several steps untill #"Renamed Columns2"
    ProductTable = Table.SelectColumns(#"Changed Type", List.Range(ColumnNames, 0, SplitPosition + 1)),
    #"Promoted Headers" = Table.PromoteHeaders(ProductTable, [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Product", type text}, {"Television", type text}, {"Phone", type text}, {"Laptop", type text}, {"Tablets", type text}, {"", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Product"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"", "Market Segment"}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Market Segment"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] = "Z")),
    #"Renamed Columns2" = Table.RenameColumns(#"Filtered Rows",{{"Attribute", "Product"}}),
    
    // Select all columns after "Market Segment" (including "Market Segment" column) into a new table CustomerTable
    // then transform the new CustomerTable with several steps untill #"Renamed Columns3"
    CustomerTable = Table.SelectColumns(#"Changed Type", List.Range(ColumnNames, SplitPosition, List.Count(ColumnNames) - SplitPosition)),
    #"Promoted Headers1" = Table.PromoteHeaders(CustomerTable, [PromoteAllScalars=true]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers1",{{"", type text}, {"Customer1", type text}, {"Customer2", type text}, {"Customer3", type text}, {"Customer4", type text}, {"Customer5", type text}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type2",{{"", "Market Segment"}}),
    #"Unpivoted Other Columns1" = Table.UnpivotOtherColumns(#"Renamed Columns1", {"Market Segment"}, "Attribute", "Value"),
    #"Filtered Rows1" = Table.SelectRows(#"Unpivoted Other Columns1", each ([Value] = "Z")),
    #"Renamed Columns3" = Table.RenameColumns(#"Filtered Rows1",{{"Attribute", "Customer"}}),
    
    // Merge above two tables #"Renamed Columns2" and #"Renamed Columns3" by "Market Segment" column
    #"Merged Queries" = Table.NestedJoin(#"Renamed Columns2", {"Market Segment"}, #"Renamed Columns3", {"Market Segment"}, "MatchingCustomers", JoinKind.LeftOuter),
    
    // Expand the merged result column and select Customer column to expand to the current table
    #"Expanded MatchingCustomers" = Table.ExpandTableColumn(#"Merged Queries", "MatchingCustomers", {"Customer"}, {"Customer"})
in
    #"Expanded MatchingCustomers"

 Output:

The pbix file is attached. Let me know if you have any questions. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!