CODEWITHSUNDEEP
powerquerym
Andrew
Andrew

Reputation: 105

How to set source of a query which name is a value of another query?

I need a query (query1) from a file in a folder. This file is daily updated and I need to connect to the newest one. In order to do that I created a query (query2) which returns the newest filename in his unique record.

Now, how to set the source of query1 as a dinamic value extracted from query 2.

In below example I want, instead of pointing to staticfilename.xlsx, to point to a dinamic filename, which value in calculated with query2

let
    Source = Excel.Workbook(File.Contents("Q:\....\staticfilename.XLSX"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(...)
in
    #"Changed Type"

Upvotes: 0

Views: 447

Answers (3)

Olly
Olly

Reputation: 7891

Another option is to use a function to return the latest file:

//fnLatestFile (excluding temp files)
(
    FileFolder as text, 
    optional FileNameContains as text, 
    optional FileExtension as text, 
    optional IncludeSubfolders as logical, 
    optional OutputType as text
) =>

let
    fSwitch =  (Expression as any, Values as list, Results as list, optional Else as any) =>
        try Results{List.PositionOf(Values, Expression)} otherwise if Else = null then "Value not found" else Else,
    Source = Table.SelectRows(Folder.Files(FileFolder), each not Text.Contains([Name], "~")),
    #"Filtered Name" = if FileNameContains = null then Source else Table.SelectRows(Source, each (Text.Contains([Name], FileNameContains) = true)),
    #"Filtered Extension" = if FileExtension = null then #"Filtered Name" else Table.SelectRows(#"Filtered Name", each ([Extension] = FileExtension)),
    #"Filtered Subfolder" = if IncludeSubfolders = true then #"Filtered Extension" else Table.SelectRows(#"Filtered Extension", each ([Folder Path] = Text.Combine({FileFolder, if Text.End(FileFolder,1) = "\" then "" else "\"}))),
    #"Sorted by Modified Date" = Table.Sort(#"Filtered Subfolder",{{"Date modified", Order.Descending}}),
    FileData = #"Sorted by Modified Date"{0},
    Output = fSwitch(
        Text.Lower(OutputType),
        {"name","fullname","date"},
        {FileData[Name], FileData[Folder Path] & FileData[Name], FileData[Date modified]},
        FileData[Content]
    )
in
    Output

Applying to your query, your first line then becomes:

Source = Excel.Workbook(fnLatestFile("Q:\....\", "staticfilename", ".xlsx", false), null, true),

Upvotes: 0

Alexis Olson
Alexis Olson

Reputation: 40204

An alternative to this is to load from a folder, sort by date created (or modified), and pick the top row instead of needing a separate query.

More details in this article and this one too.

Upvotes: 2

horseyride
horseyride

Reputation: 21373

If query2 returns a 1x1 table with column name Column1, and contents Q:\path\subpath\filename.xlsx then this should work to pull the path from query2 into your next query

Source = Excel.Workbook(File.Contents(query2{0}[Column1]), null, true),

Upvotes: 1

Related Questions