Need Help! Data transformation

[ryan_mayu]

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.

[AkhilAshok]

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]

@AkhilAshok

Thanks for your reply. Although someone has provided the solution by using DAX in this subject, i still like to learn your solution.

I tried your solution again today. Even if I chose count distinct for customer. I still got duplciated customer names after I expand the column. The results of count and distinct count are the same.

Any thoughts on that? Appreciate your help. Thanks in advance

[AkhilAshok]

If you have duplicate customer while grouping, then in Step 4, you could do like this:

= List.Distinct([AllColumns][Customer])

[ryan_mayu]

@AkhilAshok@AlB

Many thanks for your help on this. Reaaly appreciate that.

Thanks and BR

Ryan

[ryan_mayu]

@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]

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 · Accepted solution]

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]

@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]

@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]

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