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

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"
)
  • AlB's avatar
    AlB
    Community Champion
    7 years ago

    ryan_mayu

    Or another option to get the same result is adding the filtering within SUMMARIZECOLUMNS instead of at the end:

     

    Table =
ADDCOLUMNS (
    ADDCOLUMNS (
        SUMMARIZECOLUMNS (
            Table1[Shop];
            Table1[Month];
            FILTER ( ALL ( Table1[Month] ); Table1[Month] = "A" )
        );
        "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" )
)