Forum Discussion
Automatically importing files with different name into PBI
Hi Effie
When you do the load from the sharepoint folder sort files by either created or modified date - whichever works best for you. Then import binary/content for any of the target files. After this manually replace the file name in the code to #"PreviousStepName"[Name]{0}.
In the you should see something like this in the Advanced Editor:
let
Source = SharePoint.Files("https://...", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = "https://...Shared Documents/General")),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Date modified", Order.Descending}}),
file = #"Sorted Rows"{[Name= #"Sorted Rows"[Name]{0},#"Folder Path"="https://...Shared Documents/General/"]}[Content],
#"Imported Excel" = Excel.Workbook(file)
in
#"Imported Excel"
Kind regards,
JB
HiAnonymous
Thank you for your response, I appreciate it.
Apologies, I think I wasn't very clear on the output I need.
Basically, when the new file is added on the SharePoint folder, I want a new query to be created automatically on PBI, but keeping at the same time the old one.
So the Queries on Power Query Editor should look like this:
Query
Query1
Query2
Query3 etc...
where the "Query" will be using the "Report" as data source
"Query1" will be using the "Report.Current_Date_Time" as data source
"Query2" will be using the "Report.Current_Date_Time1" as data source
"Query3" will be using the "Report.Current_Date_Time2" as data source etc....
Therefore, if for example the code is the following, I need the new query to be exactly the same as the previous one, but using the relevant data scource in each different query.
let
Source = SharePoint.Contents("https:...", [ApiVersion = 15]),
RawData = Source{[Name="RawData"]}[Content],
#"Data" = RawData{[Name="Data"]}[Content],
#"BDR" = #"Data"{[Name="BDR"]}[Content],
#"Report.Current_Date_Time xlsx" = #"BDR"{[Name="Report.Current_Date_Time"]}[Content],
#"Imported Excel" = Excel.Workbook(#"Report.Current_Date_Time xlsx"),
#"Excel Output_Sheet" = #"Imported Excel"{[Item="Excel Output",Kind="Sheet"]}[Data]
in
#"Excel Output_Sheet"
I'm not sure if this is even possible, but I guess it's worth asking 🙂
Thank you again for the help.
Kind regards,
Effie
Hello Effie
its not possible that a new query is created depending on a content of a sharepoint folder.
What you can do is to create a new custom function, that takes a filename or a binary content and do whatever its needed.. in the custom function however you could check what type of file it is and create a different output depending on the file tpye (only if its needed).
After that you query the sharepoint folder, make a filtering (if needed - to exclude file you don't need in your output) and than apply you custom function.
In such a scenariao, if a new file is posted, it's automatically considered in your output.
If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun
JimmyHi Effie ,
As Jimmy already pointed out, it is not possible in PBI to automatically create a new code for every new table you have. Technically, there are only very few languages that support this feature (self-coding).
I guess, depending on the circumstances you can decide to add the "source name" table in your output query structure, so you can distinguish between different files/sources inside one table. In this case, you may also further explore "incremental" processing options to make your code only add new data to the dataset (there are some examples/references in other threads on PBI community forum).
Kind regards,
JB
