k-mean clustering table or measure

[Anonymous]

Hello,

When using the K-Maen clustering visual, can I get the cluster value of each record in a table?

Can the cluster be saved as a measure? 

Thank you,

Tamir

[TomMartens · Accepted solution]

Hey,

as you have to consider the k-means visual as a closed job, this will not be possible.

I assume, you are somewhat familiar with R, at least you already have R installed locally, and also managed to install the required R packages to make the visual work 🙂

Then you might consider to use R from inside Power Query using the 

Here is a very simple example, I use an R script as data source (Get Data --> Other --> R script):

#loads the iris dataset
data(iris)

# converts the iris dataset do a data.frame corresponds to a Power BI Table
df.iris <- as.data.frame(iris)

# removes the dataset from memory, the data.frame is still avalable
rm(iris)

This will create a table called df.iris, now i'm able to run a script from inside Power Query, of course it's not necessary to use an R script to perform the "Run R script" transformation, this is just to make R example complete, basically each table can be used as input, there can also be more than one R tasks used in one query. 

here I will use this very simple R script 

iriscluster <- kmeans(dataset[,1:4], 3,nstart = 20)
cluster <- iriscluster$cluster
df.iris <- cbind(dataset,cluster)

now my query has an additional column called cluster.

Unfortunately this approach has its drawbacks because a user can not interact (meaning filter) the initial dataset which may result to different cluster, but nevertheless you can use R to create additional information.

You also have to be aware that currently R script are currently not support by the "enterprise" mode of the on-premises gateway, just in the "personal" mode. So if you want to / have to share your data with your colleagues can become another challenge.

Hopefully this gives you an idea.

Regards,

Tom