Check if a pair of value is present in table1 and create a new column in table2

[egabor88]

Hi All

I have tried to solve this problem for a while now but got stuck, and coulnd't find a previous post about it.

I have two tables. Table1 has list of customer ids and product ids, one customer id can occur multiple times with different or same product id. Table2 has a column with the unique customer ids.

I would like to create new columns for each product id in Table 2 that returns 1 if the pair of customer id and product id is present in table1.

Example:

Order Number	Product Code
1	a
1	b
1	c
1	d
2	c
2	d
2	a
2	a
2	a
3	c
4	d
4	d

Table 2:

Order Number	a	b	c	d
1	1	1	1	1
2	1	0	1	1
3	0	0	1	0
4	0	0	0	1

I am using the below expression to create the new columns:

a = var _ordered = countx(filter('Table2', 'Table 2'[Order Number] = 'Table1'[Order Number] && 'Table1'[Product Code] = a), 'Table1'[Order Number])

return

if(not(isblank(_ordered)), 1, 0)

This creates the desired column, but i was wondering if it is possible to go through a dynamic list of product ids, and create all the required columns in one go, instead of doing it one by one. I have 69 unique product ids that can change in the future.

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order Number", Int64.Type}, {"Product Code", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each 1),
    #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[#"Product Code"]), "Product Code", "Custom", List.Min)
in
    #"Pivoted Column"

Hope this helps.

[egabor88]

Hi @Ashish_Mathur 

Thank you for your reply. This is exactly what i was looking for. Thank you very much.

[Ashish_Mathur]

You are welcome.

[barritown]

Hi @egabor88,

One of the solutions:

1) You create two tables with unique products and orders. 

For example, like this:

Orders = DISTINCT ( 'Table'[Order Number] )
Products = DISTINCT ( 'Table'[Product Code] )

2) You create one-to-many connections between the newly created tables and your fact table.

3) Once done, you add a matrix visual and compose a simple measure like this one:

Measure = 
VAR counter = COUNTROWS ( 'Table' ) + 0
RETURN IF ( counter > 0, 1, 0 )

 And it works (see the attached PBIX file if needed):

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[egabor88]

Hi @barritown 

Thank you for your reply. Unfortuantely i need a table not just a visual. I would like to make further calculations with these new columns.