From Folder, filter by specific TABs, transform TABs from each workbook differently.

[NumeritasMartin]

Good Afternoon,

I have an interesting scenario and wondering how others would solve it, I am currently looking at the route of doing each file separately but I am sure there is a better way.

Scenario is;

Multiple Excel source files with multiple TABs but only certain one's are needed, call them currency transaction TABs labelled as (EUR, USD, GBP etc.)

I am able to filter the source to those specific items from another table.

The TAB's in each workbook are slightl;y different, the date and reference column are swopped in one file casuing errors when the files are loaded together.

Ideally I'd like to

A) Get the data from one TAB and make some transformations, 'promote headers'; 'align columns' etc, apply that process to all the sel;ected TAB's in that workbook.

B) Do the same for the next workbook/s.

C) load the files together as per the usual get data from folder function.

D) Make any transformations I need to make on the whole dataset

Am I going to be stuck in doing each workbook individually?

Thanks for reading

Martin

[smpa01]

Hello @NumeritasMartin,

It is always difficult to answer without a sample data. However, I like to think that it can be achieved what you have in mind.

 I have assumed that you are looking for a batch process (for a cluster of transformation) per file. It can be done by writing s custom code and running that code on each file. For example, I have created a sample dataet as following

 and I want to run a following transformation

a) Unselect any tabs that are not USD/GBP - File level transformation

b) From each table pick up only the colums => Name/Value only - Tab level transformation

c) Promoted Headers - Tab level transformation

d) Finally produce an appened result from all the trsnformed tables from each of the tab with the name of the Tab in a separate column

(P as text)=>let
    Source = Excel.Workbook(File.Contents(P), null, true),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Name] <> "Non-Currency")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Name", "Data"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each let
   Source=[Data],
   #"Transposed Table" = Table.Transpose(Source),
    #"Sorted Rows" = Table.Sort(#"Transposed Table",{{"Column1", Order.Ascending}}),
    #"Filtered Rows1" = Table.SelectRows(#"Sorted Rows", each ([Column1] = "Value") or ([Column1] = "Name")),
    #"Transposed Table1" = Table.Transpose(#"Filtered Rows1"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true])
in
    #"Promoted Headers"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Name", "Value"}, {"Name.1", "Value"})
in
    #"Expanded Custom"

When you invoke this function please input the value of P as the path of the file. e.g. "C:\Users\Desktop\Test1\X.xlsx" but without the quoted marks on each end as this - C:\Users\Desktop\Test1\X.xlsx

[NumeritasMartin]

Thank you for the quick reply @smpa01 , your solution looks like it should work for me, I am trying to put it into application but I'm running into a little problem because I don't quite understand what steps happen when, the issue I am having is basically I have a top row to remove before the transposition step, but when I then try to filter the rows on my expected results I'm getting an error of value not found.

I put a filter in so that I can pull the TAB names from a table which works fine, I plan to do the same with the 'Columns' too.

Thanks for your help

Martin

[smpa01]

@NumeritasMartin  sory could not have come back to you earlier

Can you try this

(P as text)=>let
    //Connecting to file
    Source = Excel.Workbook(File.Contents(P), null, true),
    //Remove Unnecessary tabs, known tabs are known to the analyst
    #"Filtered Rows" = Table.SelectRows(Source, each ([Name] <> "Non-Currency")),
    //Steps to initiate transormation steps in sequence in each table
    #"Added Custom1" = Table.AddColumn(#"Filtered Rows", "Custom", each let
   Source = [Data],
   // Remove Random row that resides at row#1 from each table
   #"Removed Top Rows" = Table.Skip(Source,1),
   // Transpose Table
    #"Transposed Table" = Table.Transpose(#"Removed Top Rows"),
    #"Sorted Rows" = Table.Sort(#"Transposed Table",{{"Column1", Order.Ascending}}),
    #"Filtered Rows1" = Table.SelectRows(#"Sorted Rows", each ([Column1] = "Value") or ([Column1] = "Name")),
    #"Transposed Table1" = Table.Transpose(#"Filtered Rows1"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Name", type text}, {"Value", Int64.Type}})
in
    #"Changed Type"),
    #"Removed Other Columns1" = Table.SelectColumns(#"Added Custom1",{"Custom"}),
    #"Expanded Custom1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Custom", {"Name", "Value"}, {"Name", "Value"})
in
    #"Expanded Custom1"