Forum Discussion
Help transforming cash flow data from spreadsheets in a folder
Hello guys!
A store has a lot of cash flow spreadsheets in a folder, one by semester, containing tabs from each month, like that:
The folder
The spreadsheet, with only 5 days to be easierI can succesfully transform the data from one spreadsheet/tab:Before transforming...
After transforming
Everything ok until now, but my problem is how to do it for all the tabs in all spreadsheets...
I tried to get the data from the folder combining the spreadsheets (option combine & edit when getting the data) and I got this:
After combining the spreadsheets
And now I'm stucked!
I tried different ways to transpose this data like working with only one spreadsheet/tab above, but I can't get the same result!
Is it possible?
Maybe another way to make it possible, without working tab by tab for all spreadsheets?
Thanks!
Hi CoboleiroBr,
Based on my test, you should be able to use the M query below to Import Multiple Excel Sheets From Multiple Excel Workbooks in your scenario. :smileyhappy:
let Source = Folder.Files("C:\aaa\Desktop\New folder"), #"Removed Other Columns" = Table.SelectColumns(Source,{"Content"}), #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Excel.Workbook([Content])), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Content"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Custom.Name", "Custom.Data", "Custom.Item", "Custom.Kind", "Custom.Hidden"}), #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom",{"Custom.Name", "Custom.Kind", "Custom.Hidden", "Custom.Item"}), #"Added Custom1" = Table.AddColumn(#"Removed Columns1", "Custom", each Table.PromoteHeaders(Table.Transpose([Custom.Data]),[PromoteAllScalars=true])), #"Removed Columns2" = Table.RemoveColumns(#"Added Custom1",{"Custom.Data"}), #"Expanded Custom1" = Table.ExpandTableColumn(#"Removed Columns2", "Custom", {"Date", "Money", "Debit", "Credit"}, {"Custom.Date", "Custom.Money", "Custom.Debit", "Custom.Credit"}) in #"Expanded Custom1"Regards
4 Replies
Hi CoboleiroBr,
Based on my test, you should be able to use the M query below to Import Multiple Excel Sheets From Multiple Excel Workbooks in your scenario. :smileyhappy:
let Source = Folder.Files("C:\aaa\Desktop\New folder"), #"Removed Other Columns" = Table.SelectColumns(Source,{"Content"}), #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Excel.Workbook([Content])), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Content"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Custom.Name", "Custom.Data", "Custom.Item", "Custom.Kind", "Custom.Hidden"}), #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom",{"Custom.Name", "Custom.Kind", "Custom.Hidden", "Custom.Item"}), #"Added Custom1" = Table.AddColumn(#"Removed Columns1", "Custom", each Table.PromoteHeaders(Table.Transpose([Custom.Data]),[PromoteAllScalars=true])), #"Removed Columns2" = Table.RemoveColumns(#"Added Custom1",{"Custom.Data"}), #"Expanded Custom1" = Table.ExpandTableColumn(#"Removed Columns2", "Custom", {"Date", "Money", "Debit", "Credit"}, {"Custom.Date", "Custom.Money", "Custom.Debit", "Custom.Credit"}) in #"Expanded Custom1"Regards
Nobody??? :smileysad:
Not sure if there is anyting in Power Query to take/combine data from separate worksheets.
I use VBA and as I write VBA code time to time, if you can post a sample workbook I can provide a VBA code for you.
Thanks
