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'

AkhilAshok's avatar
AkhilAshok
Solution Sage
7 years ago

You can do this using DAX or Power Query. In DAX it will be as simple as using CONCATNATEX funciton in measure for string aggregation. You can do this approach if you want the aggregation to happen dynamically at Visual level.

 

But, if you want to already bring the data in the format you showed, then you can use Power Query Editor:

1. Go to Transform -> Group By

2. Click Advanced:

Add Shop, Month as group by columns

Add CustomerCount with Operation Count Rows, TotalAmount with operation Sum of Amount & AllColumns with operation All Rows as aggregations

3. After this you get 5 columns.

4. Go to Add Columns -> Custom Column, and give = [AllColumns][Customer]

5. Click on expand button on top right corner of the new Custom column, and select Extract Values. Select coma as the seperator.

6. Again go to Add Columns -> Custom Column, and give = if [CustomerCount]=1 then "Bad" else if [CustomerCount] <=3 then "Normal" else if [CustomerCount] >=5 then "Good" else "NA"

7. Now you can remove unnecessary columns.

 

Hope this helps.

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