Power Query: easy way to select the file the query is applied to?-CodePudding

In our lab we have a system that many people uses and produces a oddly shaped txt file with the results. I created a power query that cleans the file and I would like to share this with others (not very computer savvy) so they can apply it to the files they will generate.

What can I do to make it as easy as possible for other users to select the file they want the query to be applied to? Example: is there an easy way to create button that opens a dialog requesting the file location? Right now I have to edit the query source to select the data, this approach is clunky and will be confusing for some of my colleagues.

let
Source = Table.FromColumns({Lines.FromBinary(File.Contents("X:\foo\foo.txt"), null, null, 1252)}),
#"Removed Top Rows2" = Table.Skip(Source,32),
#"Removed Bottom Rows" = Table.RemoveLastN(#"Removed Top Rows2",16),
#"Other Steps" = ...

Thanks!

CodePudding user response：

You can directly grab a filepath from a range name cell without a function by

let
NameValue= Excel.CurrentWorkbook(){[Name="rangenamehere"]}[Content]{0}[Column1],
Source = Table.FromColumns({Lines.FromBinary(File.Contents(NameValue), null, null, 1252)}),

Or if you wanted the VBA route for file prompt

1 Create a range name, here aaa

2 Use VBA to populate it with file prompt

Sub prompt()
Dim FName As Variant
FName = Application.GetSaveAsFilename("", "Data file (*.xl*),*.xl*", 1)
If FName = False Then
MsgBox "False"
Exit Sub
End If
Range("aaa").Value = FName
End Sub

3 Refer to the named range in powerquery you set up

let
NameValue= Excel.CurrentWorkbook(){[Name="aaa"]}[Content]{0}[Column1],
Source = Table.FromColumns({Lines.FromBinary(File.Contents(NameValue), null, null, 1252)}),

4 Tack on code at end of VBA to refresh all queries or specific query

ActiveWorkbook.RefreshAll

ActiveWorkbook.Queries("QueryNameHere").Refresh

CodePudding user response：

I found this post from 2014 that works pretty well. You write a function on Query (fnGetParameter) that reads the file location from a table and then you feed it to the query that processes the data.

All the user needs to do is write the file location on the table and name and refresh.

I changed the first to lines on my PowerQuery code to look like this:

    Fileloc = fnGetParameter("File Path"),
    Source = Table.FromColumns({Lines.FromBinary(File.Contents(Fileloc), null, null, 1252)}),

Any suggestions to make it even better?