Filterering Columns with Dynamic Names

[jamuka]

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

Product	Store	Product Type
1	X1	Z2
1	X2	Z2
2	X1	Z1
2	X2	Z1
3	X2	Z2
4	X1	Z3
5	X2	Z4
2	X3	Z5
2	X5	Z7
6	X5	Z1

Updated Table

Product	Z1	Z2	Z3	Z4	Z5	Z7
1		X1 - X2
2	X1 - X2				X3	X5
3		X2
4			X1
5				X2
6	X5

My filtered table should show only Product 6.

thanks in advance and happy new year

[slorin · Accepted solution]

@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]

thank you for your help @slorin 

regards

Cem

[jamuka]

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

Product	Store	Product Type
2	X1	Z1
2	X2	Z1

Updated Table

Product	Z1
2	X1 - 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

[slorin]

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