I have created an excel file named PeopleAnalysis. Inside PeopleAnalysis, I imported source data files called People1.xlsx and People2.xlsx. With power query I made all my transformations and loaded the data into power BI and outputted my analysis. All is good. I have end users that only know how to read the outputted analysis. However, on a regular basis we will have new source data for People1.xlsx and People2.xlsx. The content of the files will be the same with the exception that there will be more or fewer rows and the filenames will be named People3.xlsx and People4.xlsx. I would like the end user to select these two filenames and then see the updated outputted analysis. What is the best way to do this?
Assuming you are connecting to Files, From Folder you could consider to filter the files in that folder to keep only the two most recent files based on their creation dates. Then, I believe you don't want to use PQ's default procedure of getting files from a folder. You would need a trick to avoid the creation of the transformation function and sample file (etc.).
Your users will not have to select the two most recent files, i.e. 3 & 4, as PQ does it for them. Can you show the query(ies) that you currently use to connect to the files 1 & 2?
Let's assume these people files all contain a structured table called "PeopleData", then you can add a custom column after you filtered for the 2 most recent files. In the example code below, that's done in a step called "Latest" where #"Sorted Row" is the name of the previous step where you sorted the files in descending order based on their creation dates.
Latest = Table.FirstN(#"Sorted Rows",2),
#"Added Custom" = Table.AddColumn(Latest, "Custom", each Excel.Workbook([Content], true, true){[Item="EmployeeData",Kind="Table"]}[Data]),
Then remove all other columns (perhaps keep the Name column as well) and extract the data from the nested tables in Custom column.
Come back here in case you get stuck.
Thank you! I will work on this today and see if I can get your suggestion to work - I like the concept of having it just pick the most recent files. I am also further looking at using a parameter within power query to identify the files to be selected. Stay tuned....
Below is what I ended up using to be able to allow an end user to update an analysis by only have to update the file path/names in excel -- no need to go into power query.
xcel file in tab named FilePaths in range a1:b3
SourceFile SourceFilePath
People HHK File C:\Users\caw\OneDrive\Desktop\PeopleReport.xlsx
Visit HHK File C:\Users\caw\OneDrive\Desktop\VisitReport.xlsx
Query named DataFiles
let
Source = Excel.CurrentWorkbook(){[Name="TDataFiles"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"SourceFile", type text}, {"SourceFilePath", type text}}),
DataFiles = [
File1Path = Table.SelectRows(#"Changed Type", each [SourceFile] = "People HHK File"){0}[SourceFilePath],
File2Path = Table.SelectRows(#"Changed Type", each [SourceFile] = "Visit HHK File"){0}[SourceFilePath]
]
in
DataFiles
Query named PeopleSource
let
ParamSource = DataFiles,
PeopleSource = ParamSource[File1Path]
in
PeopleSource
Query named PeopleInput
let
Source = Excel.Workbook(File.Contents(PeopleSource), null, true),
in
Source
Query named VisitSource
let
ParamSource = DataFiles,
FilePathVisit = ParamSource[File2Path]
in
FilePathVisit
Query named VisitInput
let
Source = Excel.Workbook(File.Contents(VisitSource), null, true),
in
Source
