Forum Discussion

ryan_mayu's avatar
ryan_mayu
Super User
7 years ago
Solved

Need Help! Data transformation

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 cu...
Need Help
  • AlB's avatar
    AlB
    7 years ago

    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'

ryan_mayu's avatar
ryan_mayu
Super User
7 years ago

AkhilAshok Thanks for your reply.

 

I followed you steps and got stucked at step 4. Appreciate that if you can provide me some screenshots to make it easier to understand. Thanks in advance.

AkhilAshok's avatar
AkhilAshok
Solution Sage
7 years ago

Can you tell me what is the code you gave why creating a new column as per Step 4? Also, please click on the error and show the error details you get.

 

 

  • AlB's avatar
    AlB
    Community Champion
    7 years ago

    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'

    • ryan_mayu's avatar
      ryan_mayu
      Super User
      7 years ago

      AlB

       

      Thanks for your help. That was really helpful. However, I have another question .Appreciate that you can help me as well.

       

      Is it possible to only keep Shop A's info in the new table?

       

      Thanks and BR

      Ryan

      • AlB's avatar
        AlB
        Community Champion
        7 years ago

        ryan_mayu

         

        Yeah, you can just add the filtering step to what we had before:

         

        Table =
FILTER (
    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" )
    );
    [Shop] = "A"
)