Make rows in new table based on column values in existing table

[Anonymous]

I have this table definition in Power BI:

CustomerProductCodes = DISTINCT(

  SELECTCOLUMNS(
    CALCULATETABLE('CustomerProduct')
      ,"CustomerID"
      ,[CustomerID]
      ,"ProductCodes"
      ,if([Toys]="T","T,","")&if([Shoes]="S","S,","")&if([Food]="F","F,","")&if([Dresses]="D","D,","")
  )
)

It will be like this:

CustomerID	ProductCodes
23	T,F,D,
44	F,D,
87	S,F,D

I don't want the codes in the same column but each code on it's own row like this:

CustomerID	ProductCodes
23	T
23	F
23	D
44	F
44	D
87	S
87	F
87	D

How do I do this in defining the table?

[tamerj1 · Accepted solution]

Hi @Anonymous 

please try

CustomerProductCodes =
DISTINCT (
GENERATE (
SUMMARIZE (
CALCULATETABLE ( 'CustomerProduct' ),
'CustomerProduct'[CustomerID],
'CustomerProduct'[Toys],
'CustomerProduct'[Shoes],
'CustomerProduct'[Food],
'CustomerProduct'[Dresses]
),
UNION (
SELECTCOLUMNS (
{ 'CustomerProduct'[Toys] },
"ProductCodes", IF ( [Toys] = "T", "T" )
),
SELECTCOLUMNS (
{ 'CustomerProduct'[Shoes] },
"ProductCodes", IF ( [Shoes] = "S", "S" )
),
SELECTCOLUMNS (
{ 'CustomerProduct'[Food] },
"ProductCodes", IF ( [Food] = "F", "F" )
),
SELECTCOLUMNS (
{ 'CustomerProduct'[Dresses] },
"ProductCodes", IF ( [Dresses] = "D", "D" )
)
)
)
)

[tamerj1 · Accepted solution]

Hi @Anonymous 

please try

CustomerProductCodes =
DISTINCT (
GENERATE (
SUMMARIZE (
CALCULATETABLE ( 'CustomerProduct' ),
'CustomerProduct'[CustomerID],
'CustomerProduct'[Toys],
'CustomerProduct'[Shoes],
'CustomerProduct'[Food],
'CustomerProduct'[Dresses]
),
UNION (
SELECTCOLUMNS (
{ 'CustomerProduct'[Toys] },
"ProductCodes", IF ( [Toys] = "T", "T" )
),
SELECTCOLUMNS (
{ 'CustomerProduct'[Shoes] },
"ProductCodes", IF ( [Shoes] = "S", "S" )
),
SELECTCOLUMNS (
{ 'CustomerProduct'[Food] },
"ProductCodes", IF ( [Food] = "F", "F" )
),
SELECTCOLUMNS (
{ 'CustomerProduct'[Dresses] },
"ProductCodes", IF ( [Dresses] = "D", "D" )
)
)
)
)

[Anonymous]

I forgot that in CustomerProduct I have a column with YearMonth. The Customers buy several things in different months.

It will be like this when I add YearMonth in spec:

YearMonth	CustomerID	ProductCodes
202401	23	T,F,D,
202401	44	F,D,
202401	87	S,F,D,
202402	44	D,T,
202402	87	F,D,

I want YearMonth in the CustomerProductCodes table, too. How do I do that?

[Anonymous · Accepted solution]

Hi @tamerj1 ,thanks for the quick reply, I'll add further.

Hi @Anonymous ,

Regarding your question, the table data are as follows.

Try to modify your DAX expression.

Table = DISTINCT(
    SELECTCOLUMNS(
    CALCULATETABLE('CustomerProduct'),
    "YearMonth",[YearMonth],
    "CustomerID",[CusTomerID],
    "ProductCodes",if([Toys]="T","T,","")&if([Shoes]="S","S,","")&if([Food]="F","F,","")&if([Dresses]="D","D,","")
    )
)

You can also use Power Query if you are trying to split.Copy the table data to Excel, use 'Get data' and click 'Transform data'.

Details can be found in the following articles.

Split columns by delimiter - Power Query | Microsoft Learn

Use the following DAX expression to create a Table

Table = FILTER('Sheet3','Sheet3'[ProductCodes] <> BLANK())

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.