Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
BjornarHallset
Regular Visitor

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

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:

CustomerIDProductCodes
23T,F,D,
44F,D,
87S,F,D

 

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

 

CustomerIDProductCodes
23T
23F
23D
44F
44D
87S
87F
87D

 

How do I do this in defining the table?

2 ACCEPTED SOLUTIONS
tamerj1
Super User
Super User

Hi @BjornarHallset 

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" )
)
)
)
)

View solution in original post

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

Hi @BjornarHallset ,

Regarding your question, the table data are as follows.

vzhouwenmsft_0-1713164532786.png

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,","")
    )
)

vzhouwenmsft_1-1713164599124.png

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

vzhouwenmsft_2-1713164868479.png

Details can be found in the following articles.

Split columns by delimiter - Power Query | Microsoft Learn

vzhouwenmsft_5-1713165075113.png

vzhouwenmsft_6-1713165098907.png

vzhouwenmsft_7-1713165118990.png

 

Use the following DAX expression to create a Table

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

vzhouwenmsft_8-1713165219178.png

 

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.

View solution in original post

3 REPLIES 3
tamerj1
Super User
Super User

Hi @BjornarHallset 

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" )
)
)
)
)

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:

YearMonthCustomerIDProductCodes
20240123T,F,D,
20240144F,D,
20240187S,F,D,
20240244D,T,
20240287F,D,

 

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

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

Hi @BjornarHallset ,

Regarding your question, the table data are as follows.

vzhouwenmsft_0-1713164532786.png

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,","")
    )
)

vzhouwenmsft_1-1713164599124.png

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

vzhouwenmsft_2-1713164868479.png

Details can be found in the following articles.

Split columns by delimiter - Power Query | Microsoft Learn

vzhouwenmsft_5-1713165075113.png

vzhouwenmsft_6-1713165098907.png

vzhouwenmsft_7-1713165118990.png

 

Use the following DAX expression to create a Table

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

vzhouwenmsft_8-1713165219178.png

 

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.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors