Automatically importing files with different name into PBI

[Syndicate_Admin]

Hi all,

I have created an MS flow that when I recieve a specific email, takes the attachement and saves it on a SharePoint folder. The attachment's name is always stored on different name as I don't want the old file in the folder to be replaced, just to add the new attachement. A new file is scheduled to be created every day. So the SharePoint folder looks something like this:

Report

Report.Current_Date_Time

Report.Current_Date_Time1

Report.Current_Date_Time2 etc...

The issue with saving the new files with different name is that PBI doesn't pick them up when automatically refreshing, as the query only includes the file named "Report" and therefore I would have to manually change the code. 

Therefore, what I would ideally want to do is, when the new file is created in the folder eg. "Report.Current_Date_Time", a new query is created on PBI using as datasource this new file ("Report.Current_Date_Time"). 

Is there any way of doing this? I would really appreciate your help. 

Thank you in advance,

Effie

[Syndicate_Admin]

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

[Syndicate_Admin]

Hi@jborro 

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

[Syndicate_Admin]

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

Jimmy

[Syndicate_Admin]

Hi @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

[Syndicate_Admin]

Hi Effie,

I have recently had the same / similar issue. I think that this youtube tutorial may help you:
https://www.youtube.com/watch?v=8H9xM741hBQ