Dataflow - Failed to analyze issues in the query

[aJayMitchell]

Good afternoon,

I've been working on setting up a dataflow to pull paginated data from an OData feed. I've tested this on a relatively small dataset (~4,000 rows) and now want to test this on a larger dataset (~190,000 rows). On updating the OData source to the larger source, I now get the following error (from the new style 'Please wait' dialogue) when trying to 'Save & Close' the dataflow:

Given I know that this dataflow should work (the change from the smaller data source to the larger one is just changing the company name) - it's frustrating that I can't save it and set it to refresh.

Is this a known issue, and is there any workaround?

The code for the dataflow is:

let
  initReq = Json.Document(Web.Contents(
    "***startofODatasourceaddress***",
    [RelativePath="Company('Norway')/BI_GeneralLedgerEntries?$format=json"]
  )),
  nextUrl = initReq[#"odata.nextLink"] & "&$format=json",
  initValue = initReq[value],
  gather = (data as list, url)=>
let
relPath = Text.End(url,Text.Length(url)-Text.Length("***startofODatasourceaddress***")),
newReq=Json.Document(Web.Contents(
    "***startofODatasourceaddress***",
    [RelativePath=relPath]
  )),
newNextUrl = newReq[#"odata.nextLink"] & "&$format=json",
newData= newReq[value],
data=List.Combine({data,newData}),
Converttotable = Record.ToTable(newReq),
Pivot_Columns = Table.Pivot(Converttotable, List.Distinct(Converttotable[Name]), "Name", "Value", List.Count),
Column_Names=Table.ColumnNames(Pivot_Columns),
Contains_Column=List.Contains(Column_Names,"odata.nextLink"),
check = if Contains_Column = true then @gather(data, newNextUrl) else data
in
check,
  Converttotable = Record.ToTable(initReq),
  Pivot_Columns = Table.Pivot(Converttotable, List.Distinct(Converttotable[Name]), "Name", "Value", List.Count),
  Column_Names = Table.ColumnNames(Pivot_Columns),
  Constain_Column = List.Contains(Column_Names, "odata.nextLink"),
  outputList = if Constain_Column= true then @gather(initValue,nextUrl) else initValue,
  expand = Table.FromRecords(outputList)
in
  expand

[ChrisClegg]

I had the same issue.

It worked OK with a small dataset - for development - but failed with a larger one.

The approach I took was to set a parameter to either 'Development' or 'Production' and then limited the number of rows when it was set to development.  I then needed to externalise that parameter, because I couldn't change it without changing the dataflow and seeing the same problem again.  So I created an external table to keep the parameter values in and changed the parameter in the dataflow to a query that produced the necessary text from the loaded external table.

That's a workaround (kludge!) but it works.  I can then save the dataflow when it's in development mode ok; and externally update it to be in production.  The refresh works fine.

Note that the Gen2 dataflows don't seem to have this problem.

[dataguy-ali]

I also face the same issue but could not find any solution yet!

[Anonymous]

Hi,

Is there any update in this regard?

We created DEV & TEST dataflows where we were fetching only a few records from Cosmos. Everything was working fine. So, we created PBI reports using the previously mentioned dataflows. But, for the UAT environment, when we removed filters (conditions in where clause) and tried to save these changes in dataflow, neither we can save this dataflow, nor refresh it.

Thanks

[darrenfishell]

I have the same exact issue and frustration. My query runs just fine across a subset of pages and also in PowerBI Desktop, but it only returns the "Failed to Analyze" issue against the full dataset. With the subset of data, the analyze step is very quick. 

[lbendlin]

You can raise an issue at https://issues.powerbi.com . If you have a Pro license you can consider raising a Pro ticket at https://powerbi.microsoft.com/en-us/support/pro/

[juliarwk]

iI have the exact same issue! the query works just fine in PowerBI desktop and on the dataflow, but whenI hit "Save and Close" the error "failed to analyze issues in the query" shows up