Forum Discussion

bloigge's avatar
bloigge
Advocate IV
11 years ago
Solved

Refreshing queries with functions doens't work

Does anyone else experience that refreshing on-premise data via a gateway (for example amount of files in a folder) works if you hardcode the path to the folder, but when you create a function where ...
miguel's avatar
miguel
Community Admin
11 years ago

I've tried using a function from an Azure Machine Learning Web Service and it works perfectly. I've tried others that work over a set of tables from Excel Files and also from a SQL Database table and they work great as well.

 

Is there any chance that you can paste the M code here so we can take a look?

  • bloigge's avatar
    bloigge
    Advocate IV
    11 years ago

    Hardcoded Folder Path Query is working - Gateway options are shown:

     

     

    let
    Source = Folder.Files("P:\MyFolder\MySubFolder"),
    #"Sorted Rows" = Table.Sort(Source,{{"Date created", Order.Descending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1),
    #"Filtered Rows" = Table.SelectRows(#"Added Index", each ([Index] = 0)),
    tempPath = #"Filtered Rows"{0}[Folder Path],
    folderPath = Text.Range(Text.Range(tempPath,0,Text.Length(tempPath)-1),Text.PositionOfAny(Text.Range(tempPath,0,Text.Length(tempPath)-1),{"\"},Occurrence.Last) + 1,(Text.Length(tempPath)-1) - (Text.PositionOfAny(Text.Range(tempPath,0,Text.Length(tempPath)-1),{"\"},Occurrence.Last) + 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Content", "Name", "Extension", "Date accessed", "Date modified", "Attributes", "Folder Path", "Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Date created", folderPath}})
in
    #"Renamed Columns"

     

     

    Creating a function which I then recall multiple times is not working:

     

     

    let
    Abfrage = (path as text) =>
let
    Source = Folder.Files(path),
    #"Sorted Rows" = Table.Sort(Source,{{"Date created", Order.Descending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1),
    #"Filtered Rows" = Table.SelectRows(#"Added Index", each ([Index] = 0)),
    tempPath = #"Filtered Rows"{0}[Folder Path],
    folderPath = Text.Range(Text.Range(tempPath,0,Text.Length(tempPath)-1),Text.PositionOfAny(Text.Range(tempPath,0,Text.Length(tempPath)-1),{"\"},Occurrence.Last) + 1,(Text.Length(tempPath)-1) - (Text.PositionOfAny(Text.Range(tempPath,0,Text.Length(tempPath)-1),{"\"},Occurrence.Last) + 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Content", "Name", "Extension", "Date accessed", "Date modified", "Attributes", "Folder Path", "Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Date created", folderPath}})
in
    #"Renamed Columns"
in
    Abfrage

     

     

    Calling the function:

     

    let
    Source = GetLatestFile("P:\MyFolder\MySubFolder")
in
    Source

     

    The notification I get when using a function:

    Capture.PNG