Forum Discussion
Need Help! Data transformation
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'
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.
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
If you have duplicate customer while grouping, then in Step 4, you could do like this:
= List.Distinct([AllColumns][Customer])