Forum Discussion
From Folder, filter by specific TABs, transform TABs from each workbook differently.
Community ChampionHello 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
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"
