Unable to refresh data with Power BI Service

[MaBlaise]

Hello Power BI community,

I have the following message when I tried to refresh dataset : Désolé... Nous n'avons pas pu convertir la valeur "[Function]" en type Function.

A table-valued function is used by the query.

But, it works when using a view.

Here's the details about query used :

let

    Source = Sql.Database("cubetest", "Astea"),

    dbo_FCT_VALO_ITV = Source{[Schema="dbo",Item="FCT_VALO_ITV"]}[Data],

    FCT_VALO_ITV = dbo_FCT_VALO_ITV(BeginDate),

    #"Renamed Columns" = Table.RenameColumns(FCT_VALO_ITV,{{"z_udf_string_2", "Num PTO reçue"}, {"sa_person_id", "Id_tech"}, {"actual_dt", "Date fin ITV"}, {"request_id", "ID INTER"}, {"actual_tm", "Heure fin ITV"}, {"arrive_dt", "DATE RV"}, {"arrive_tm", "Heure début ITV"}, {"pcode_id", "Typologie_Cor"}, {"cust_refno1", "REF PARTENAIRE"}, {"callt_id", "Type raccordement"}, {"first_name", "PRENOM"}, {"last_name", "NOM"}, {"name_prefix_id", "entreprise_STT"}, {"cst_id_externe_tech", "REF_PARTENAIRE"}, {"caller_name", "NOM Client"}, {"phone_id", "TELEPHONE"}, {"open_date", "DATE IN"}, {"cst_bytel_cpe_city", "VILLE"}, {"cst_bytel_cpe_address_l1", "ADRESSE"}, {"cst_bytel_cpe_zip", "DEP"}, {"descr_lang3", "StatutOT"}, {"nb_photos", "Nb_Photos"}}),

    #"Merged alpha1" = Table.NestedJoin(#"Renamed Columns",{"Typologie_Cor"},alpha1,{"pcode_id"},"alpha1",JoinKind.LeftOuter),

    #"Expanded alpha1" = Table.ExpandTableColumn(#"Merged alpha1", "alpha1", {"à afficher"}, {"Nb Fibre"}),

    #"Merged alpha2" = Table.NestedJoin(#"Expanded alpha1",{"bpart_id"},alpha2,{"bpart_id"},"alpha2",JoinKind.LeftOuter),

    #"Expanded alpha2" = Table.ExpandTableColumn(#"Merged alpha2", "alpha2", {"à afficher"}, {"MODELE_INTER"}),

    #"Merged num_pto_transmis" = Table.NestedJoin(#"Expanded alpha2",{"order_id"},Checklists,{"order_id"},"Checklists",JoinKind.LeftOuter),

    #"Expanded num_pto_transmis" = Table.ExpandTableColumn(#"Merged num_pto_transmis", "Checklists", {"NumPto"}, {"merged.Num PTO transmis"})

in

    #"Expanded num_pto_transmis"

Thanks in advance,

MaBlaise.

[v-frfei-msft]

Hi @MaBlaise,

As the information you shared, you used sql database as your data source.

1.Did you add the data source to your gateway in power BI service?

2.Did you use the same verification method as the Power Bi Desktop side?

3.How about updating to the latest version of the gateway and try agian?

Regards,

Frank

[MaBlaise · Accepted solution]

Hi @v-frfei-msft,

Thanks for your answer.

But all of your 3 points are well configures.

Here's a new information : the report use two datasources, one with SQL-Server in Gateway, and one Online Excel file. A query merge is used.

With this query merge, I have attribut from alpha1 in FCT_VALO_ITV item, and in visualisations only FCT_VALO_ITV is present, alpha1 is hidden.

After delete query merge, and re-active relationship between FCT_VALO_ITV and alpha1, refreshing data works !

The only thing that change is visualisations pane, because alpha1 is visible now, and we have to use attribute from this datasource.