Hi,
Has anyone could connect the Power Bi to Kinetic on cloud?
on previous versions (I mean ERP 10 and 9) I was using data gateway directly to Ms SQL and SQL queries could pull all the information I required, seamlessly it was getting updated with the schedules i configured in Data Gateway.
Now we are on Kinetic Public Cloud and I have to use the Rest API and found the whole connections works with API keys through IDP. With some help documents I could managed to get data on version API v1 only with from BO services using power query. I am passing Client ID, Client secret and client URL generated from IDP. I am getting data for Erp.BO.PartSvc, Parts list etc. But I was looking for BAQ services and couldnt succeed.
With API help page and Odata feed and result was same.
Is there any other way or does any have the correct syntax,steps which connect the BAQ (with/without) parameters.
Power Query
let
idp_base_url="https://login.epicor.com",
client_id = "3c3b414d-clientid",
client_secret="4m8gQO+10mbsjw-client_secret",
erp_api_base_url="https://uksouthdtpilot02.epicorsaas.com/SaaSPilot",
/*erp_odata_get_url_part = "/api/v2/odata/companyID/BaqSvc/ADP_IT_CUS/Data",*/
erp_odata_get_url_part = "/api/v1/Erp.BO.PartSvc/List",
/* MultiPart Post code from: https://gist.github.com/CurtHagenlocher/b21ce9cddf54e3807317 */
Web.MultiPartPost = (url as text, parts as record) as binary =>
let
unique = Text.NewGuid(),
boundary = "--" & unique,
crlf = "#(cr)#(lf)",
item = (name, value) =>
let
filename = Value.Metadata(value)[name]?,
contentType = Value.Metadata(value)[type]?,
line1 = "Content-Disposition: form-data; name=""" & name & """" &
(if filename = null then "" else "; filename=""" & filename & """"),
line2 = if contentType = null then {} else { "Content-Type: " & contentType },
lines = { boundary, line1 } & line2 & { "", value }
in
Text.Combine(lines, crlf) & crlf,
body = Text.Combine(List.Transform(Record.FieldNames(parts), each item(_, Record.Field(parts, _)))) & boundary & "--" & crlf
in
Web.Contents(url, [
Headers=[#"Content-Type"="multipart/form-data; boundary=" & unique],
Content=Text.ToBinary(body)
]),
parts = [
grant_type = "client_credentials",
scope = "epicor_erp",
client_id = client_id,
client_secret = client_secret
],
result = Json.Document(Web.MultiPartPost(idp_base_url & "/connect/token", parts)),
resultToken= result[access_token],
erpResult = Json.Document(Web.Contents(erp_api_base_url,
[
Headers=[#"Authorization"="Bearer " & resultToken],
RelativePath = erp_odata_get_url_part
]))[value],
tabularValue = Table.FromList(erpResult, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(tabularValue, "Column1", {"Company", "PartNum", "PartDescription", "ClassID", "TypeCode"}, {"Column1.Company", "Column1.PartNum", "Column1.PartDescription", "Column1.ClassID", "Column1.TypeCode"})
in
#"Expanded Column1"
