Forum Discussion

jamuka's avatar
jamuka
Helper IV
1 year ago
Solved

Filterering Columns with Dynamic Names

Hello all,

 

I have table with product and store information. I transform this table to another table, first grouped based on Product Type, then pivoted it. Then I filtered Product Type Z1  where other Product Types are null.

 

My problem is Product Types are changing and even in some case there's no Z1 Product Type therefore my hardcoded formula gives error.

 

Current Table

 

ProductStoreProduct Type
1X1Z2
1X2Z2
2X1Z1
2X2Z1
3X2Z2
4X1Z3
5X2Z4
2X3Z5
2X5Z7
6X5Z1

 

 

 

Updated Table

ProductZ1Z2Z3Z4Z5Z7
1 X1 - X2    
2X1 - X2   X3X5
3 X2    
4  X1   
5   X2  
6X5     

 

My filtered table should show only Product 6.

 

thanks in advance and happy new year

Need Help
  • slorin's avatar
    slorin
    1 year ago

    jamuka 

    let
    Source = YourSource,
    Group = Table.Group(Source, {"Product"}, 
        {{"Product Type List", each List.Distinct([Product Type])},
         {"Store", each Text.Combine([Store], "-")}}),
    Filter = Table.SelectRows(Group, each [Product Type List]={"Z1"})
    in
    Filter

    Stéphane 

4 Replies

  • slorin's avatar
    slorin
    Super User
    1 year ago

    Hi jamuka 

     

    let
    Source = YourSource
    Group = Table.Group(Source, {"Product"}, {{"Product Type List", each [Product Type]}, {"Data", each _}}),
    Filter = Table.SelectRows(Group, each [Product Type List]={"Z1"}),
    Combine = Table.Combine(Filter[Data])
    in
    Combine 

    Stéphane 

  • slorin's avatar
    slorin
    Super User
    1 year ago

    jamuka 

    let
    Source = YourSource,
    Group = Table.Group(Source, {"Product"}, 
        {{"Product Type List", each List.Distinct([Product Type])},
         {"Store", each Text.Combine([Store], "-")}}),
    Filter = Table.SelectRows(Group, each [Product Type List]={"Z1"})
    in
    Filter

    Stéphane 

  • jamuka's avatar
    jamuka
    Helper IV
    1 year ago

    Hi slorin,

     

    thank  you for your help, your code works but if a product have two different store it didn't combine stores and

    didn't show it in final table.

     

    Current Table

    ProductStoreProduct Type
    2X1Z1
    2X2Z1

     

     

    Updated Table

    ProductZ1
    2X1 - X2

     

    my current (hard coded) formula is

     

     

    let
    Source = YourSource
    
	#"Grouped Rows" = Table.Group(#"Source", {"Product", "Product Type"}, {{"Table", each Text.Combine([Store],"-"), type nullable text}}),

	#"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[#"Product Type"]), "Product Type", "Table"),
    
	#"Filtered Rows" = Table.SelectRows(#"Pivoted Column", each ([Z1] <> null) and ([Z2] = null) and ([Z3] = null) and ([Z4] = null) and ([Z5] = null) and ([Z7] = null))

in
    #"Filtered Rows"

     

     

    kind regards

    Cem