Power Query with Dynamic Data Source

[Mplz]

Hello All,

I am building a report template to send to outside branches which have some not so tech savvy users. I'm trying to automate as much as I can so the end users only need to click a couple buttons that I've assigned to macros and have PQ/VBA do the rest.

Each week a report is ran that dumps data into a mess of a txt file. I am using power query to clean up the data and load it into Excel. I have a VBA script that allows the user to navigate to the txt file report and import it into the correct sheet. However, I have yet to figure out a way to then run the power query steps to clean it up after it's imported. Currently I am going into the PQ advanced editor and changing the filepath to the new report file.

Is there a way to hard code the M code into VBA and run it on the applicable worksheet? Can I have the end users save the query but have the source dynamically change to the current worksheet after the report has been imported? I would ideally like to avoid having the user save the query as I would then have to help them change the filepath as it will be a different location for every user. Below is my VBA and M code, any help would be greatly appreciated!

Sub PaidHours()

Dim FileToOpen As Variant
Dim Openbook As Workbook
Dim ShName As String
Dim Sh As Worksheet

FileToOpen = Application.GetOpenFilename(Title:="Browse for your File & Select Sheet", FileFilter:="Excel Files (*.xls*),*.xls*")
Application.ScreenUpdating = False
Application.DisplayAlerts = False

If FileToOpen <> False Then
    Set Openbook = Application.Workbooks.Open(FileToOpen)
    ShName = Application.InputBox("Enter the sheet name to copy", "Enter the sheet name to copy")
    For Each Sh In Openbook.Worksheets
        If UCase(Sh.Name) Like "*" & UCase(ShName) & "*" Then
            ShName = Sh.Name
        End If
    Next Sh

    'copy data from the specified sheet to this workbook - updae range as you see fit
    Openbook.Sheets(ShName).Range("A1:CF1100").Copy
    ThisWorkbook.Worksheets("Paid Hours").Range("A1").PasteSpecial xlPasteValues
    Openbook.Close False
End If
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

let
    Source = Csv.Document(File.Contents("C:\Users\TRD6855\Documents\RTA\Mountain\Accountability Report\Accountability 7-13.txt"),[Delimiter=",", Columns=1, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Positions" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByPositions({0, 13, 16, 19, 27, 31, 42, 48, 59, 65, 72, 86, 91, 103, 121, 124, 128}), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8", "Column1.9", "Column1.10", "Column1.11", "Column1.12", "Column1.13", "Column1.14", "Column1.15", "Column1.16", "Column1.17"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Positions",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}, {"Column1.4", type text}, {"Column1.5", type text}, {"Column1.6", type text}, {"Column1.7", type text}, {"Column1.8", type text}, {"Column1.9", type text}, {"Column1.10", type text}, {"Column1.11", type text}, {"Column1.12", type text}, {"Column1.13", type text}, {"Column1.14", type text}, {"Column1.15", type text}, {"Column1.16", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Actual Time", each [Column1.10]),
    #"Trimmed Text" = Table.TransformColumns(#"Added Custom",{{"Actual Time", Text.Trim, type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Trimmed Text",":",".",Replacer.ReplaceText,{"Actual Time"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value",{{"Actual Time", type number}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type2", "MECH NUM", each Text.End([Column1.1],7))
in
    #"Added Custom1"

Quick note: I am using the same PaidHours sub to import payroll info as well, using "("Text Files (*.txt), *.txt")" as the file filter on a duplicate sub. I know the one I pasted is for excel files only.

 

[ExcelToDAX]

Question, why do you have your users opening the file and having it paste values? Why not just have the Power Query code get the file and manipulate it? Instead of having VBA paste the values and having Power Query trying to find the current worksheet? In my opinion, I would create the Power Query code with a dynamic link and have the users paste the file path in an Excel NamedRange so that it would manipulate it.

 

[Mplz]

Question, why do you have your users opening the file and having it paste values? Why not just have the Power Query code get the file and manipulate it? Instead of having VBA paste the values and having Power Query trying to find the current worksheet? In my opinion, I would create the Power Query code with a dynamic link and have the users paste the file path in an Excel NamedRange so that it would manipulate it.

I'm new to power query so I'm not really sure the full capability of it.

There are two separate files I need the users to import, one is an xls and one is a text file. The xls I am manipulating after the import, but the power query one I need to import and run the query steps to transform it into usable data in excel. In a perfect world, they click an "import hours" button, navigate to the hours xls file and it dumps it in. Then they click an "import data" button, it imports the txt file and runs the query steps on it. I don't want to set myself up for calls each week on how to find the filepath and paste it into the correct cell in excel if possible, because I know they will not know how to find it or keep it organized. There has to be a way to dynamically change the source file in the query, but I have scoured the internet and haven't found a way to do what I want. I am also fully aware this is my ignorance with PQ functionality so if I can get pointed in the right direction I'm happy to poke around.

 

[alansidman]

You can put the file paths in a dropdown and have the users select the file path that you want. Then using that build a parameter query where the variable file path is automatically put into your Query.

 

[Alex Blakenburg]

I am unclear as to where the VBA and Query reside. I assume they are in the Template that the user is opening.
As far as I can tell in the video @alansidman has suggested you need to run the parameter query and have the destinations pre-defined for the dropdown. (Liked the video though)

Since you are already using VBA perhaps you can populate a cell that is a named range with the Folder & File name and use that in the query.

Change the Power Query source based on a cell value

Discover how to change the Power Query source based on a cell value. Amazing technique to build simple solutions for others to use.

exceloffthegrid.com