User function no longer works after changing datasource from xlsx to csv

[Stefano66]

I use Power Query and M Language to manipulate data during my work, I'm not a professional.

I started from an Excel file with many sheets containing my source data. I build a query which call a user function returning a record of data with two fields, here is the function whose name is ValoreNormale:

(dataRiferimento as date) as record =>
    let
        minDate=Date.AddMonths(dataRiferimento,-1),
        Quot = Table.SelectRows(#"Quotazioni elaborate", each ([Date] >= minDate and [Date] < dataRiferimento)),
        QuotMedia = List.Average(Table.Column(Quot, "Quotazione EUR")),
        QuotMediaPond = List.Sum(Table.Column(Quot, "Volume valorizzato EUR"))/List.Sum(Table.Column(Quot, "Volume")),
        rValoreNormale=Record.FromList({QuotMedia, QuotMediaPond}, type [QuotazioneMedia = number, QuotazioneMediaPonderata = number])
              in
        rValoreNormale

in the calling query the function is called in the following way

= Table.AddColumn(#"Aggiunta colonna personalizzata", "Quotazione media", each ValoreNormale([Data]))

where [Data] is the field containing the date to be sent as a parameter to the function. 

Everything works fine and I've got the record from the function back in the added column of the query.

Due to the extremely slowness of query calculation, to improve performance I moved all sheets containing source data to single csv files, then connected them to a newly created blank xlsx file and pasted all the code of the queries I need, including the above user function and the query using it.

What happens now is that if I call the function from the Power Query editor passing a date for dataRiferimento parameter I still get the correct result as a record, whilst in the calling query I get an error:

Expression.Error: We cannot convert type value "[Function]" to type function.
Details:
Value=[Function]
Type=[Type]

I tried to analyze the function behavior  line by line and it seems the issue is in

Quot = Table.SelectRows(#"Quotazioni elaborate", each ([Date] >= minDate and [Date] < dataRiferimento))

If I put the result of this line in the returning value of the function, I get the error in the calling query (but still not in the Power Query Editor).

"Quotazioni elaborate" is another query that performs some calculation on the records of a source table; and another user function based on "Quotazioni elaborate" has the same issue. However, "Quotazioni elaborate" by itself produces its result (a table) without any error.

Here is Quotazioni elaborate code:

let
    Origine = Quotazioni,
    #"Rimosse colonne" = Table.RemoveColumns(Origine,{"Open", "High", "Low"}),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Rimosse colonne", "Volume valorizzato", each [Volume]*[Close], type number),
    #"Merge di query eseguito" = Table.NestedJoin(#"Aggiunta colonna personalizzata", {"Date"}, Table.SelectRows(Cambi, each [Valuta] = "GBP"), {"Data"}, "Cambi", JoinKind.LeftOuter),
    #"Tabella Cambi espansa" = Table.ExpandTableColumn(#"Merge di query eseguito", "Cambi", {"Cambio"}, {"Cambio"}),
    #"Aggiunta colonna personalizzata1" = Table.AddColumn(#"Tabella Cambi espansa", "Quotazione EUR", each [Close]/100/[Cambio], type number),
    #"Aggiunta colonna personalizzata2" = Table.AddColumn(#"Aggiunta colonna personalizzata1", "Volume valorizzato EUR", each [Volume valorizzato]/100/[Cambio], type number)
in
    #"Aggiunta colonna personalizzata2"

Quotazioni contains the source data from csv file and has the following fields: {"Date", type date}, {"Open", type number}, {"High", type number}, {"Low", type number}, {"Close", type number}, {"Adj Close", type number}, {"Volume", Int64.Type}}

Instead of calling the function from my query and putting the resulting record in a newly added column, I tried, then, to create this column using directly the line who seems to be the problem:

#"Aggiunta colonna personalizzata10" = Table.AddColumn(#"Modificato tipo", "Personalizzato", each Table.SelectRows(#"Quotazioni elaborate", each ([Date] >= Date.AddMonths([Data],-1) and [Date] < [Data])))

This statements executes the first two lines of the above function. And I get a table as a result, not an error.

I have no idea how to solve this issue and I can't understand why the same code:

• works if source data are in Excel sheets and not if they come from csv files
• works in PowerQuery editor regardless of the source and not if the function is used in other queries
• if sources are csv files, works if inserted directly in the calling query and not if used in a called function

Any help will be really appreciated.

Thanks in advance

Stefano

[AlB]

Hi @Stefano66 

I tend to agree with @lbendlin 

If, however, you share the input files you are using (xlsx, csv) plus the M code (preferably the pbix where it is being used), perhaps we can find a solution. You'd have to share the URL to the files hosted elsewhere: Dropbox, Onedrive... or just upload the file to a site like tinyupload.com (no sign-up required).

Saluti

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[lbendlin]

If you are beginning to work with Power Query you may not want to start with multiple nested calls and calls to sources that are outside the function context - that's rather complex.  Better to begin with a linear query that includes all the steps, and then convert parts of the query to functions if needed.