Import external excel files table data using parameters (Power Query) Headscratcher!

[andydtaylor]

Hi,

I've been scratching my head on a problem which I can't figure out a nice solution to. I hope an expert can give me a steer on the steps I should take!

I have a main parent model file which needs to import data from child models. The folder containing the input modules is on the same path as the main model:

main model
/input_modules/
---loans.xlsm
---tb.xlsm
---PR.xlsm
---[future files]

Each input module (child model) contains a table called o_Export on a sheet called o_Export. The columns contain some commonality (e.g. Date, Amount, Description) and some columns unique to the particular input module. Below is a screen grab of how I have the control laid out at the moment in the main model.

https://drive.google.com/open?id=1-czgh5H4KNlolfpM0yuQ3ap_75DL4Hjl


I want to implement either of the below possibilities:

Option 1: Combine each "o_Export" tables found in each excel file in that input_modules folder
Option 2: Specifically import the o_Export table from each input module file name as specified in my main model control sheet
Or Option 3 something equivalent I haven't thought of.


I've gone in circles for a few hours and I'm getting a bit perplexed by functions and parameters and single queries versus separate queries and I'm not sure how to make this work. But I feel there must be a way! Clearly there needs to be some kind of control to prevent errors - with Option 2 for example I need to dismiss input modules without a file name and not try to import these. A key capability I want to incorporate is the ability to add a new input module(child model) which contains an o_Export table and have this consolidated by the main model. I'm happy for there to be a need to specify the file name in the main model controls.

I look forward to some advice! I'm quite familiar with much of Excel and vba but confess I have not read much on Power Query or M so I am still learning by doing.

Many thanks,

Andy

 

[Matt Allington]

Option 1 should work out of the box using the combine feature. I have a blog explaining how to combine files https://exceleratorbi.com.au/understanding-power-query-combine/. I don’t see an issue with the different column names, as long as you make sure there are no references to the headings in the sample file query. If there are any, simply delete them.

There are manual processes too that I cover here. https://exceleratorbi.com.au/combine-excel-workbooks-power-query-method-1/

 

[andydtaylor]

Hi Matt,

Thanks for your reply. I noticed my office version wasn't updating and the current version of PQ seems to have straightened out some issues. However the columns I get in my consolidated output seem to be limited to the columns in the first file read. Also, after selecting it in the dialogue boxes this new version of PQ gives me, I can't see how this code is singling out a table called 'o_Export' in each of these files.

Please can you suggest how I can enforce this requirement in the code generated, below and additionally remove any limits on the columns returned? As I said in my post above there are common columns but some additional columns in certain input files.

Also to recap my goal is that any file with an o_Export table dropped into this folder will have its contents swept up to main model.

Many thanks,


Andy

let
    Source = Folder.Files("C:\Users\IBM\Desktop\model versions\0509v1\input_modules"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from input_modules", each #"Transform File from input_modules"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from input_modules"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from input_modules", Table.ColumnNames(#"Transform File from input_modules"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Source", type text}, {"Month", type date}, {"Description", type text}, {"Amount", type number}, {"Source_wb", type text}})
in
    #"Changed Type"

 

[Matt Allington]

Well you need to look at the code to work out what is happening. if you refer to the first blog I posted above, you will see the sample query and the combine query. It is important that the sample query doesn't do anything to prevent columns from other files being removed. The combine query then needs to not do anything to ignore the extra columns either. The "Removed Other Columns1" step above looks suspicious to me - that could definitely be the culprit. You should be able to delete this OK. Also recheck the "Expanded Table Column1" step, and make sure you click "Load More" if that option exists. That could also be the culprit.

To test it, I suggest build the query with all file types, but then remove the first file from the source folder and then refresh preview. This will force PQ to show you what will happen when a different file type is loaded. I understand it is not easy, and there is no single answer - you simply have to learn to read what the PQ steps are doing and be clear on the implications.

 

[andydtaylor]

Hi Matt,

Thanks for your help with this.

I now have Power Query successfully importing data from an o_Export table for each Excel file in the inputs folder. I also have the resulting not 100% same columns being dynamically expanded for any new file placed in the source folder, so no new column is left out.

One issue has presented itself as I have tested this - can you recommend a solution?

Issue: If I have a file in the source folder which does not contain an o_export table the query fails.


I'd hoped of course that I would get away with this without consequence... Can I effect some form of failing silently/skipping files in the queries or fIM function and can you make a suggestion as to how this might be accomplished?

The meat of the data retrieval seems to come from the below

1. Transform Sample File from input_modules

let
    Source = Excel.Workbook(#"Sample File Parameter1", null, true),
    o_Export_Table = Source{[Item="o_Export",Kind="Table"]}[Data]
in
    o_Export_Table

2. The function fIM

let
    Source = (#"Sample File Parameter1") => let
        Source = Excel.Workbook(#"Sample File Parameter1", null, true),
        o_Export_Table = Source{[Item="o_Export",Kind="Table"]}[Data]
    in
        o_Export_Table
in
    Source

3. Input modules Query:

let

// Get path
    Pathparameter = Excel.CurrentWorkbook(){[Name="sys_im_parameters"]}[Content],
    Pathparameter_value = Pathparameter{0}[Column1],

//main query

    Source = Folder.Files(Pathparameter_value),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "fIM", each fIM([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "fIM"}),
    #"Expanded Transform File from input_modules" = ExpandAll(#"Removed Other Columns1")

in

#"Expanded Transform File from input_modules"

Many Thanks,


Andy

 

[andydtaylor]

I just tried adding this to the function fIM, and it now works, with Error handling:

let
    Source = (#"Sample File Parameter1" as binary) => let


//original code

    Source = Excel.Workbook(#"Sample File Parameter1", null, true),
    o_Export_Table = Source{[Item="o_Export",Kind="Table"]}[Data],

//end of original code

AlternativeOutput=#table(type table [Description=text,Source=text,Amount=Int64.Type],
      {{"Error", "Error", 0}}),
    //Does the Source step return an error?
    TestForError= try o_Export_Table,
    //If Source returns an error then return the alternative table output
    //else return the value of the #"Changed Type" step
    Output = if TestForError[HasError] then AlternativeOutput else o_Export_Table
in
    Output
in
    Source

Credit: https://blog.crossjoin.co.uk/2014/09/18/handling-data-source-errors-in-power-query/

 

[Matt Allington]

Great. Or you can simple apply some filter that removes the files that don't meet the rules. This would work if there was some suitable naming convention. Also I think there is some "skip files with errors" in the query combine wizard.