Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
ryan_mayu
Super User
Super User

Need Help! Data transformation

‎12-15-2018 11:51 PM

Hi all,

 

I have a set of data and want to transform to a new data model in PowerBI. Below is the screenshot of data transformation.

 

There are two difficult parts for me:

1. combine all the customers and use comma to separate them.

2. Add a type for each shop. 

Number of customer number=1, "Bad"

1<Number of customer number<=3, "Normal"

Nubmber of customer number>=5, "Good"

 

Thanks in advance.

 

Capture.JPG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Labels:
Message 1 of 11
1,088 Views
0
1 ACCEPTED SOLUTION
AlB
Super User
Super User
In response to AkhilAshok

‎12-16-2018 03:47 PM

Hi @ryan_mayu

@AkhilAshok's solution looks really good. If you want to do it with DAX instead, you could create a table based in yours with something like this:

 

Table =
ADDCOLUMNS (
    ADDCOLUMNS (
        SUMMARIZECOLUMNS ( Table1[Shop]; Table1[Month] );
        "Customer"; CALCULATE (
            CONCATENATEX ( VALUES ( Table1[Customer] ); Table1[Customer]; ", " )
        );
        "Amount"; CALCULATE ( SUMX ( Table1; Table1[Amount] ) )
    );
    "Type";
    VAR NumCustomers =
        LEN ( [Customer] ) - LEN ( SUBSTITUTE ( [Customer]; ","; "" ) )
            + 1
    RETURN
        SWITCH ( NumCustomers; 1; "Bad"; 2; "Normal"; 3; "Normal"; "Good" )
)

Where it is assumed that number of customers >=4 is of type 'Good'

View solution in original post

Message 5 of 11
1,017 Views
10 REPLIES 10

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

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.

View All