Forum Discussion

Yass_Pbi's avatar
Yass_Pbi
Frequent Visitor
3 years ago

Use Web.contents( ) with Query & RelativePath parameters for Json Data List in Power Query M

Hello,

 

I want to parametrize this URL using Relative Path and Query functions inside Custom M Function, here my URL and my code:

URL = "http://newapi.omni.com/1.13/Omni.svc/GetRelationListJson?json={TypeIDs: 10,ObjectID: "& property_id &",LanguageId: 2, CustomerName: CustName, CustomerPassword :Pass}"

property_id = Parameter used as Invoke Custom Function 

 

Source = (Property as number) as table =>

let
    property_id = Number.ToText(Property),
   

    Source = Json.Document(Web.Contents(URL_BODY,
   [RelativePath= "GetRelationListJson?json={TypeIDs: 10,ObjectID: "& property_id &",LanguageId: 2, CustomerName: CustName, CustomerPassword :Pass}"])),

......

 

This work only in Power BI Desktop, I have error in Power BI Service : [Unable to combine Data] ......

 

To fix this issue, I folowed CHRIS WEBB Blog, but unfortunately he didn't gave us an example with a json list.

I tried this, but it's not working: 

 

Source = Json.Document(Web.Contents(URL_BODY,
[RelativePath= "GetRelationListJson",
Query =
          [
            json=
                    {
                      TypeIDs= 1,
                      ObjectID= property_id,
                      LanguageId= 2,
                      CustomerName= "Cust",
                      CustomerPassword= "Pass"
                    }

            ]

 

Any idea on how to handle with list of Json inside Query function of URL please?

 

Thank's in advance for your help

6 Replies

  • Yass_Pbi's avatar
    Yass_Pbi
    Frequent Visitor

    I tried this: 

    Source = Json.Document(Web.Contents(URL_BODY,
    [RelativePath= "GetRelationListJson",
    Query =
              [
               json=
               "{
                  ""TypeIDs"": ""1"",
                   ""ObjectID"":" & property_id&",
                   ""LanguageId"": ""2"",
                   ""CustomerName"": ""Cust"",
                   ""CustomerPassword"": ""Pass""
                   }"

                ]
          ])),

     

    It works in PBI Desktop, but I'm facing the same error in PBI Service: 

    [UNABLE to Combine Data] ............ 

     

    Anyone can help please?

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi Yass_Pbi ,

     

    You can go to:

    File=>Options and Settings=>Options and set the Ignore Privacy Levels option.

     

    If 'Privacy Settings' can not be your workaround, maybe you can try to use data flows.

    For more information, you can refer:

    https://www.meanttobi.com/power-bi-refresh-error-unable-to-combine-data-workaround/

     

    You can refer to the following posts that may be helpful to you:

    Online Refresh Error when desktop refresh works fi... - Microsoft Power BI Community

     Define and invoke function within query - Microsoft Power BI Community

     Unable to Combine Data - Custom Function - Microsoft Power BI Community

     

    Best Regards,

    Neeko Tang

    If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

    • Yass_Pbi's avatar
      Yass_Pbi
      Frequent Visitor

      Hello Anonymous ,

       

      Thank for different links.

       

      I've tried all these workaround before, Unfortunately I have the same error in Power BI Service:

      1. Combine all different queries in single one query.

      2. Use Query parameter with Relative Path in URL

      3. Set Privacy Setting in PBI Desktop to (Always Ignore Privacy Level).

       

       

       

      • Yass_Pbi's avatar
        Yass_Pbi
        Frequent Visitor

        Hi all,

         

        It works only with DataFlow, which means I need to upgrade all the licenses of my customer (Pro to PPU). It's very weird that we are obliged to use DataFlow to handle with this issue