Forum Discussion

NumeritasMartin's avatar
NumeritasMartin
Frequent Visitor
7 years ago

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

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.   ...
General Comment
Need Help
Show and Tell
Tips and Tricks
NumeritasMartin's avatar
NumeritasMartin
Frequent Visitor
7 years ago

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's avatar
smpa01
Icon for Community Champion rankCommunity Champion
7 years ago

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"