API query returns different results in Dataflow than in PowerBI Desktop

[darrenfishell]

I have a PowerQuery Dataflow that has been functioning properly for months but was recently found to be missing records. To test, I copied the exact M script and ran it in PowerBI Desktop, finding all of the expected records returned. 

I am puzzled that these return inconsistent results and hesitant to use Dataflows for this purpose, though it's a better solution long-term as this data source will be used in multiple models.

The query involves the steps below, with separate functions that retrieve auth tokens and paginated API results: 

let
  static_workflow_types = #"Query Setup",
  api_token = getAuth(),
  get_pages = Table.AddColumn(static_workflow_types, "page_table", each getWorkflowPages(api_token, [workflow], [status], [date_range])),
  remove_blank_pages = Table.RemoveRowsWithErrors(get_pages, {"page_table"}),
  workflow_and_pages = Table.ExpandTableColumn(remove_blank_pages, "page_table", {"page"}, {"page"}),
  int_pages = Table.TransformColumnTypes(workflow_and_pages, {{"page", Int64.Type}}),
  retrieve_details = Table.AddColumn(int_pages, "data", each getWorkflowDetails([page], api_token, [workflow], [status], [date_range])),
  add_table = Table.AddColumn(retrieve_details, "data2", each Table.FromRecords([data][firmFlowReportResponse])),
  remove_data_errors = Table.RemoveRowsWithErrors(add_table, {"data2"}),
  unpack_columns = Table.Combine( remove_data_errors[data2] ),
  add_info = Table.AddColumn(unpack_columns, "info_field_data", each Table.FromRecords([informationFields])),
  add_steps = Table.AddColumn(add_info, "step_data", each Table.FromRecords([routingSummary])),
  pivot_cols = Table.AddColumn(add_steps, "info_field_data_pivoted", each Table.Pivot(Table.TransformColumnTypes([info_field_data], {{"name", type text}}), List.Distinct(Table.TransformColumnTypes([info_field_data], {{"name", type text}})[name]), "name", "value")),
  pivot_col_names = Table.ColumnNames( Table.Combine(pivot_cols[info_field_data_pivoted] )),
  expand_cols = Table.ExpandTableColumn(pivot_cols, "info_field_data_pivoted", pivot_col_names),
  drop_info = Table.RemoveColumns(expand_cols, {"info_field_data", "informationFields", "routingSummary"}),
  add_key = Table.AddColumn(drop_info, "surrogate_key", each Text.Combine({[clientNumber], [filingID], [deliverables]}, "-")),
  remove_dupes = Table.Distinct(add_key, {"surrogate_key"})
in
  remove_dupes

[v-cazheng-msft]

Hi @darrenfishell 

May I know whether you found records missing in Edit Queries page or a report page created on the dataflow? Have you ever refreshed this dataflow? Could you please check what kind of records missing from it and whether it is impacted by some filters you set?

Best Regards,

Community Support Team _ Caiyun

[darrenfishell]

Thank you @v-cazheng-msft. 

The records were missing in the Dataflow. I found them missing both in the step editor within the Dataflow editor online and also in the Edit Queries page when connected to the Dataflow. 

I copy/pasted the M script from the Dataflow into a blank query in PowerBI Desktop and found the records appeared. 

So, it was the exact same M script, but with different results in the Dataflow editor and the PowerQuery editor on Desktop.