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

[Yass_Pbi]

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

[v-tangjie-msft]

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]

Hello @v-tangjie-msft ,

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]

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

[v-tangjie-msft]

Hi @Yass_Pbi ,

Has your problem been solved? If the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out. Thanks in advance.

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]

Hello @v-tangjie-msft ,

I found a workaround ( Using DataFlow), so it's not solved yet with Power Query.

Best regards, 

[Yass_Pbi]

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?