Consume Microsoft Cluster API using PowerBI

Question

Thanks for getting back to me.

Basically I subscribed to a Cluster API service (cortana analytics). This is the sample application as per Microsoft Machine Learning site

http://microsoftazuremachinelearning.azurewebsites.net/ClusterModel.aspx

As you could see there are 2 arguments to be passed on

Input

K

Where input could be 10;5;2,18;1;6,7;5;5,22;3;4,12;2;1,10;3;4 (each row is separated by semi colon)

And K is cluster number: 5 (for example)

So to consume this API I use PowerBI Edit Query,

So go to Get Data > More > Azure > Microsoft Data MarketPlace, I can see the list of APIs I subscribed to, one of them is the one I referred to in the link above.

So I load that as Function lets called it "Score"

Then I got energy table which I loaded in from a csv file, I want to cluster energy consumption into 5 clusters.

So my data layout is

Year Energy

2001 6.28213

2002 14.12845

2003 5.55851

and so on, lets say I got 100 rows of the data.

So I tried to pass "6.28213;14.12845;5.55851", "5" to Score function but I dont know how to

1. Convert my table into records

2. pass 2 argument records and constant value 5 as K.

Hope this makes sense.

Please help! :)

Thank you in advance.

Answer 1

To convert a column of numbers into a semicolon delimited text, do this to your table:

1. Convert your Energy column is type text.
2. Add [Energy] after the name of your table, which gives you a list of the numbers.
3. Use Text.Combine to turn the list into a text value seperated by ;

Here's a mashup that does that:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NcjBCQAgDAPAXfKWYqKR7iLdfw1F8J63N9Q70bBCKQ5Ue6VbnEHl9L9xz2GniaoD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Year = _t, Energy = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Energy", type text}}),
    Custom1 = #"Changed Type"[Energy],
    Custom2 = Text.Combine(Custom1, ";")
in
    Custom2

---

Once you have a function, you'll invoke it like YourFunction(Custum2, 5)