Table.Distinct breaks refresh publish report

[Anonymous]

Hi,

I'm building a report which uses and combines multiple queries from agile CRM. The data is stored in the cloud, so no gateway is being used.

Basically I want to extract all different owners linked to the records from my different queries.

E.g

let
    Source = Json.Document(Web.Contents("https://agilecrmbi.azurewebsites.net/api/agilecrm/contacts")),
    cont1 = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    cont2 = Table.ExpandRecordColumn(cont1, "Column1", {"owner"}, {"owner"})
    cont3 = cont2[owner],
    cont4 = List.RemoveNulls(cont3),
    cont5 = Table.FromList(cont4, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    cont6 = Table.ExpandRecordColumn(cont5, "Column1", {"id", "domain", "email", "phone", "name", "pic", "schedule_id", "calendar_url", "calendarURL"}),
    cont7 = Table.TransformColumnTypes(cont6, {{"id", Int64.Type}, {"domain", type text}, {"email", type text}, {"phone", type any}, {"name", type text}, {"pic", type text}, {"schedule_id", type text}, {"calendar_url", type text}, {"calendarURL", type text}}),
    cont8 = Table.Distinct(cont7, "id"),

    Source = Json.Document(Web.Contents("https://agilecrmbi.azurewebsites.net/api/agilecrm/companies")),
    comp1 = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    comp2 = Table.ExpandRecordColumn(comp2, "Column1", {"owner"},{"owner"})
    comp3 = comp2[owner],
    comp4 = List.RemoveNulls(comp3),
    comp5 = Table.FromList(comp4, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    comp6 = Table.ExpandRecordColumn(comp5, "Column1", {"id", "domain", "email", "phone", "name", "pic", "schedule_id", "calendar_url", "calendarURL"}),
    comp7 = Table.TransformColumnTypes(comp6, {{"id", Int64.Type}, {"domain", type text}, {"email", type text}, {"phone", type any}, {"name", type text}, {"pic", type text}, {"schedule_id", type text}, {"calendar_url", type text}, {"calendarURL", type text}}),
    comp8 = Table.Distinct(comp7, "id"),

    allOwners1 = Table.Combine(cont8, comp8),
    allOwners2 = Table.Distinct(allOwners1, "id")
in
    cont6

I have tried several approaches:

- Completely expand and extract each query seperatly, get unique records, combine all results.

- Combine results, expand, extract, distinct.

- ...

All these approaches work fine in the desktop application, but when I publish this and try to refresh my dataset gives the followong error message:

Data source error: We cannot convert the value "[Record]" to type Record.. The exception was raised by the IDataReader interface. Table: Owners.

I have been adding my code line by line, publishing it and refreshing it, and it all works fine until the moment I add a line that uses Table.Distinct()

Is this a bug or can someone explain how to make this work?

Kr,

Koen

[v-yuta-msft]

Hi KWillaert,

Does the issue also occurs in power bi desktop once you add the line ?

comp8 = Table.Distinct(comp7, "id")

Regards,

Jimmy Tao