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 o...
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 

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