Forum Discussion

minhvuong93's avatar
minhvuong93
Helper II
8 years ago
Solved

How to Pivot a calculated table?

Hello guys, So I have a Sales Database with 4 key  columns: outlet code, product code, order quantity and month. My table name is MK.   I would like to pivot the month to the columns and then ext...
Need Help
Tutorial Requests
  • v-piga-msft's avatar
    v-piga-msft
    8 years ago

    Hi minhvuong93,

     

    For your requirement, it seems that use R script should be a good choice. I have tests with 20 thousands data, it will spend seconds that should faster than using dax or pivot table.

     

    Before you run Rscript, you should install R engine. The details you could have a look at this article.

     

    You could prepare your data in Power Editor. After installing the R engine, you could click  Run R Script under Transform.

     

    Then you could copy the expression below and paste it in the Script box.

     

    library(reshape2)
result <- dcast(dataset, OutletCode + ProductCode ~ month, fun.aggregate = sum, value.var = "OrderQuantity")

     

    For reference, you could have a look at the pbix I have attached below.

     

    Hope it can help you!

     

    Best Regards,

    Cherry

minhvuong93's avatar
minhvuong93
Helper II
8 years ago

v-piga-msft

Thanks for your reply,

I havent been able to do it, could you be a little more specifict? :(

AFter installing: I clicked on the "R" symbol and select "enable".

And then what am I supposed to do next, is it dragiing the column into this?

v-piga-msft's avatar
v-piga-msft
Resident Rockstar
8 years ago

Hi minhvuong93,

 

Assuming that you have installed the R and enable it.

 

As I mentioned above, Go to Query Editor, you could click  Run R Script under Transform. Then you could copy the expression below and paste it in the Script box.

 

library(reshape2)
result <- dcast(dataset, OutletCode + ProductCode ~ month, fun.aggregate = sum, value.var = "OrderQuantity")

Then Close && apply and create the visual.

 

Best Regards,

Cherry