Forum Discussion

David Narovec's avatar
David Narovec
Copper Contributor
Aug 17, 2018

Power Query Source Folder

Hi Everyone, 

 

I am currently working on a project in my job in which I am trying to simplify the analysis of several reports. To be exact there are 12 reports which are validated against 1 report. 

 

I am working with the power query in order to format the reports so they are useful.

 

My issue is the sourcing, as the reports change every day and also that each report looks different, hence i need to load them separate and cant load them in one go.

 

So the scenario is the following:
I have one folder in which the reports are loaded every day, but unfortunately the names change and hence the query looses the "path" 

 

E.g. RG0040_20180701 changes to RG0040_20180702

 

The first part is always the same, but the date within the file name changes. 

 

I mean I could rename all reports every day, which would be rather annoying, especially since I am working on shortening the process as much as possible.

 

Does anyone by any chance have a suggestion on how to resolve this issue? Maybe with a code which tells the query to look for only the name (eg. RG0040) as this is already a unique identifier to the report?

 

Many thanks in advance for your valuable feedback. 


David

BI & Data Analysis
excel
Excel Online
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Aug 17, 2018

    Hi David,

     

    Yes, if all files are different you may use From Folder connector and filter your files based on its name start. The code looks like

    let

pMyFolder = "C:\Test",
pMyFileID = "RG0040",
pMyTable = "Table1",

    Source = Folder.Files(pMyFolder),
    FilterFile = Table.SelectRows(Source, each Text.StartsWith([Name], pMyFileID)),
    KeepContent = Table.SelectColumns(FilterFile,{"Content"}),
    Content = KeepContent{0}[Content],
    GetContent = Excel.Workbook(Content),
    GetTable = Table.SelectRows(GetContent, each ([Name] = pMyTable)),
    RemoveUnused = Table.SelectColumns(GetTable,{"Data"}),
    Expandtable = Table.ExpandTableColumn(RemoveUnused, "Data", {"Date 1", "Date 2", "Value"}, {"Date 1", "Date 2", "Value"})
in
    Expandtable

    Better to define first 3 as parameters not to hardcode them within the query. And above query could be fully generated from UI.