Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I am a new bee in PBI. I have tried TM1 Restpi in GetData-> Web-> Advanced option in PBI Desktop. 'Get' Method mdx query executed succesfully and pulled the dimension elements. But now I tried to make POST request in MDX query, in fiddler it says
"message=The content type specified is not supported. Please use JSON (application/json; charset=utf-8) as the content type."
Though I have changed the content-type, same error.
Here is my code:
let
url = "http://localhost:8000/api/v1/ExecuteMDX?$expand=Axes($select=Ordinal;$expand=Tuples($count;$expand=Members($select=Name))),Cells($select=Ordinal,Value)",
body = "{
""MDX"": ""SELECT {([kh_bgkto].[zTest1])} on 0,{([kh_ges].[test])} on 1 FROM [kh_bg] WHERE ([kh_verf].[KV1],[kh_jahr].[J02],[kh_perio].[P12C],[kh_versi].[V01],[kh_kons].[B01],[kh_konz].[KF01])""
}",
Parsed_JSON = Json.Document(body),
BuildQueryString = Uri.BuildQueryString(Parsed_JSON),
Source = Json.Document(Web.Contents(url,
[
Headers= [ #"Authorization"="Basic sddRtaW46", #"ContentType"="application/json; charset=UTF-8"], Content = Text.ToBinary(body)
]
))
in
#"Source"
Thanks in advance
Solved! Go to Solution.
Thanks for your support@v-lid-msft
I have removed the paresdJSON.
Fianlly the below code works for me and return the value:
let
url = "http://host:8000/api/v1/ExecuteMDX?$expand=Axes($select=Ordinal;$expand=Tuples($count;$expand=Members($select=Name))),Cells($select=Ordinal,Value)",
body = "{
""MDX"": ""SELECT {([kh_bgkto].[zTest1])} on 0,{([kh_ges].[test])} on 1 FROM [kh_bg] WHERE ([kh_verf].[KV1],[kh_jahr].[J02],[kh_perio].[P12C],[kh_versi].[V01],[kh_kons].[B01],[kh_konz].[KF01])""
}",
Source = Json.Document(Web.Contents(url, [Headers=[#"Authorization"="Basic YWRtaW46", #"Content-Type"="application/json"], Content = Text.ToBinary(body)
])),
in
Source
Hi @Anonymous ,
We can try to put the json into the body by using the following queries to meet your requirement:
let
url = "http://localhost:8000/api/v1/ExecuteMDX?$expand=Axes($select=Ordinal;$expand=Tuples($count;$expand=Members($select=Name))),Cells($select=Ordinal,Value)",
body = "{
""MDX"": ""SELECT {([kh_bgkto].[zTest1])} on 0,{([kh_ges].[test])} on 1 FROM [kh_bg] WHERE ([kh_verf].[KV1],[kh_jahr].[J02],[kh_perio].[P12C],[kh_versi].[V01],[kh_kons].[B01],[kh_konz].[KF01])""
}",
Parsed_JSON = Json.Document(body),
Source = Json.Document(Web.Contents(url,
[
Headers= [ #"Authorization"="Basic sddRtaW46", #"ContentType"="application/json; charset=UTF-8"], Content = Parsed_JSON
]
))
in
#"Source"
Have you tried to verify the request by posting this request with other tools?
Best regards,
Hi,
Thanks for your reply.
I have tried your solution, but end up with "Expression.Error: We cannot convert a value of type Record to type Text."
Any suggestion on this.
Hi @Anonymous ,
Sorry for our late reply, Could you please try to use the following query ?
let
url = "http://localhost:8000/api/v1/ExecuteMDX?$expand=Axes($select=Ordinal;$expand=Tuples($count;$expand=Members($select=Name))),Cells($select=Ordinal,Value)",
body = "{
""MDX"": ""SELECT {([kh_bgkto].[zTest1])} on 0,{([kh_ges].[test])} on 1 FROM [kh_bg] WHERE ([kh_verf].[KV1],[kh_jahr].[J02],[kh_perio].[P12C],[kh_versi].[V01],[kh_kons].[B01],[kh_konz].[KF01])""
}",
Parsed_JSON = Binary.FromText(body),
Source = Json.Document(Web.Contents(url,
[
Headers= [ #"Authorization"="Basic sddRtaW46", #"ContentType"="application/json; charset=UTF-8"], Content = Parsed_JSON
]
))
in
#"Source"
Best regards,
Hi, Thanks for your reply.
Again Expression.Error: Invalid binary encoding.
Details:
{
"MDX": "SELECT {([kh_bgkto].[zTest1])} on 0,{([kh_ges].[test])} on 1 FROM [kh_bg] WHERE ([kh_verf].[KV1],[kh_jahr].[J02],[kh_perio].[P12C],[kh_versi].[V01],[kh_kons].[B01],[kh_konz].[KF01])"
}
Any suggestions. Thanks
Thanks for your support@v-lid-msft
I have removed the paresdJSON.
Fianlly the below code works for me and return the value:
let
url = "http://host:8000/api/v1/ExecuteMDX?$expand=Axes($select=Ordinal;$expand=Tuples($count;$expand=Members($select=Name))),Cells($select=Ordinal,Value)",
body = "{
""MDX"": ""SELECT {([kh_bgkto].[zTest1])} on 0,{([kh_ges].[test])} on 1 FROM [kh_bg] WHERE ([kh_verf].[KV1],[kh_jahr].[J02],[kh_perio].[P12C],[kh_versi].[V01],[kh_kons].[B01],[kh_konz].[KF01])""
}",
Source = Json.Document(Web.Contents(url, [Headers=[#"Authorization"="Basic YWRtaW46", #"Content-Type"="application/json"], Content = Text.ToBinary(body)
])),
in
Source
Hi @Anonymous ,
First of all, we suggest you to remove the confidential information in your query, such as server address and authen key.
Glad to hear that you have resolved your problem. It seems chang the Binary.FromText to Text.ToBinary works, Thank you for sharing this wonderful solution, would you please kindly mark your sharing solution as an answer so that it can benefit more users?
If you have any other questions about this scenario, please kindly ask here and we will try to resolve it.
Best regards,
Hello @v-lid-msft ,
Once again thanks for the support.
As you suggested, when I remove the credentials , its saying the below error:
Is it a security breach or its not safe enough to expose credentials in the url in Power BI. User info is the encoded one.
Hi @Anonymous ,
Apology for our misleading reply, we want to suggest you to delete the credential of the query in your reply of this forum. Please keep your actual query if it has been working successfully.
Best regards,