Filter using power query

[SamWhite]

https://docs.google.com/spreadsheets/d/1_Z-IpqJsgiRmppJEr8jPHn6VUnfOpHPB/edit?usp=drive_link&ouid=11... 

I have data like above:

I want to filter a product which has Z and get all the corresponding customers. I want to perform this for all the products. Like if I filter for Tablets, I should get Customer1, Customer2, Customer4 and Customer5. How to achieve this using power query?

Sample file attached.

[dufoq3]

Hi @SamWhite, different approach here:

Result

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]),
    PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    BlankColumns = List.Transform(List.PositionOf(List.Transform(Table.ToColumns(PromotedHeaders), (x)=> List.IsEmpty(List.Select(x, (y)=> not List.Contains({null, ""}, Text.Trim(y))))), true, Occurrence.All), (w)=> Table.ColumnNames(PromotedHeaders){w}),
    StepBack = PromotedHeaders,
    RemovedBlankColumns = Table.RemoveColumns(StepBack, BlankColumns),
    Helper = [ ColNames = Table.ColumnNames(RemovedBlankColumns),
    SplitColumnPosition = List.PositionOf(List.Transform(Table.ToColumns(RemovedBlankColumns), (x)=> List.Count(List.Select(x, (y)=> not List.Contains({null, "Z", ""}, Text.Trim(y)))) > 0), true),
    SplitColumn = ColNames{SplitColumnPosition},
    ProductColumns = List.Transform({0..SplitColumnPosition-1}, (x)=> ColNames{x}),
    CustomerColumns = List.Transform({ SplitColumnPosition + 1..Table.ColumnCount(StepBack2)-1 }, (x)=> ColNames{x}) ],
    StepBack2 = RemovedBlankColumns,
    ReplaceValues = Table.ReplaceValue(StepBack2, 
        each [ a = List.PositionOf(Record.ToList(Record.SelectFields(_, Helper[ProductColumns])), "Z", Occurrence.All),
               b = List.Transform(a, (x)=> Helper[ProductColumns]{x})
             ][b],
        each null,
        (x,y,z)=> if x = "Z" then y else null,
        Helper[CustomerColumns] ),
    RemovedColumns = Table.RemoveColumns(ReplaceValues, Helper[ProductColumns]),
    UnpivotedColumns = Table.UnpivotOtherColumns(RemovedColumns, {Helper[SplitColumn]}, "Customer", "Product"),
    ExpandedValue = Table.ExpandListColumn(UnpivotedColumns, "Product"),
    RenamedColumns = Table.RenameColumns(ExpandedValue,{{Helper[SplitColumn], "Type"}})
in
    RenamedColumns

[Anonymous · Accepted solution]

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!

[SamWhite]

Thanks @Anonymous , please explain a bit more of what steps to perform in the power query editor.

I have tried to load the data, however the columns names are not showing correctly as shown below, even if I promote the first row as header it will show the column names as Column1, Column2 except for the Market Segment column.

[Anonymous]

Hi @SamWhite 

The result in your second image is expected. You can do the following operations to use my code:

1. Please notice that my previous step before comment "// Get all column names" is #"Changed Type" but yours is #"Changed Type1", so the easiest is that you can delete the first "Changed Type" step in your Applied Steps pane, then rename the last step "Changed Type1" to "Changed Type". 

2. Open your query's Advanced Editor, add a comma at the end of #"Changed Type" step, remove the following code "in #"Changed Type"", then paste all my steps from the comment "// Get all column names" to the end into Advanced Editor, just below #"Changed Type" step.

3. Save the code in Advanced Editor and close it. You will have the expected outcome. 

Best Regards,
Jing