Edit Queries - Folders

[lamorim]

Hi, everyone

I ask for some help or tips to resolve this problem.

I am using the "folder" option to Get Data from various Excel Workbook. They all look the same, as the information that I need are in the same plane in all the files.

But to extract the data that I want, I do need to transform the Workbook (Remove Rows and clean some information). I am not having any issue until this moment.

My main problem is when I add more files to the folder. I can not find a way to transform all the new files.

Are there any way to Transform one of the files and when I add more files to the folder automatically apply the sames changes to all of them? If Yes, How?

Thank you and best regards 

[tjd]

Could you please post your PQL (M) code from the Advanced Query Editor so we can see how you are importing your files, along with a sample copy of the file names in your folder?  Can't really help you out until we see how the files are structured and how you're importing them.

[lamorim]

Thanks for your reply

You can find one of the files here: https://1drv.ms/x/s!ApTBU_xmB2gAgRn3hWmLgmcRbFZj

And the M code here

let
    Source = Folder.Files("I:\Dados Power_BI\Dados\Teste"),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Extension", "Date accessed", "Date modified", "Date created", "Folder Path"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each Excel.Workbook([Content])),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Content", "Attributes"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns1","Name",Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, false),{"Name.1", "Name.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Name.1", type text}, {"Name.2", type text}}),
    #"Split Column by Position" = Table.SplitColumn(#"Changed Type","Name.2",Splitter.SplitTextByPositions({0, 17}, false),{"Name.2.1", "Name.2.2"}),
    #"Split Column by Position1" = Table.SplitColumn(#"Split Column by Position","Name.2.2",Splitter.SplitTextByPositions({0, 11}, false),{"Name.2.2.1", "Name.2.2.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position1",{{"Name.2.1", type text}, {"Name.2.2.1", type date}, {"Name.2.2.2", type text}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Changed Type1",{"Name.2.1", "Name.2.2.2"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns2", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Custom.Name", "Custom.Data", "Custom.Item", "Custom.Kind", "Custom.Hidden"}),
    #"Expanded Custom.Data" = Table.ExpandTableColumn(#"Expanded Custom", "Custom.Data", {"Column1", "Column2", "Column3", "Column4"}, {"Custom.Data.Column1", "Custom.Data.Column2", "Custom.Data.Column3", "Custom.Data.Column4"}),
    #"Removed Columns3" = Table.RemoveColumns(#"Expanded Custom.Data",{"Custom.Name", "Custom.Item", "Custom.Kind", "Custom.Hidden"}),
    #"Removed Top Rows" = Table.Skip(#"Removed Columns3",17),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows"),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Column2", "Dia"}}),
    #"Removed Top Rows1" = Table.Skip(#"Renamed Columns",2),
    #"Removed Bottom Rows" = Table.RemoveLastN(#"Removed Top Rows1",3),
    #"Removed Columns4" = Table.RemoveColumns(#"Removed Bottom Rows",{"Dia"}),
    #"Split Column by Position2" = Table.SplitColumn(Table.TransformColumnTypes(#"Removed Columns4", {{"Day", type text}}, "en-GB"),"Day",Splitter.SplitTextByPositions({0, 4}, false),{"Day.1", "Day.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Position2",{{"Day.1", Int64.Type}, {"Day.2", Int64.Type}}),
    #"Split Column by Position3" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type2", {{"Day.2", type text}}, "en-GB"),"Day.2",Splitter.SplitTextByPositions({0, 1}, false),{"Day.2.1", "Day.2.2"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Position3",{{"Day.2.1", Int64.Type}, {"Day.2.2", Int64.Type}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type3",{{"Day.1", "Ano"}, {"Day.2.1", "Mês"}, {"Day.2.2", "Dia"}}),
    #"Changed Type4" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Mês", Currency.Type}})
in
    #"Changed Type4"

[tjd · Accepted solution]

I loaded your original file and was able to get the query to work.  Then, I made a copy of the file and renamed it to the next month.  This time, the query failed.  From what I could determine, it failed because after the last footer row of the first file, you now have the header (and footer) info from the second file.  However, you are using a "remove top 17" and "remove bottom 2" to get rid of the header and footer info.  What you need to do is set up your query initially using at least two files so you can see how the data are repeating and then add in some cleanup like "remove duplicates", "sort", etc. to get all your headers and footers together and then delete them all at once before you begin your transformations.

I was able to do the following on the column custom.data.1:

1. Sort descending - puts all the text in the column at the top

2. Remove nulls - right click and "Remove Empty"

3. Convert to whole number - all text rows in the column get labeled "Error"

4. Remove Rows|Remove Errors, or right click and "Remove Errors"

This resulted in 60 rows of data.  Since steps 3 and 4 removed the original headers, you'll have to use column rename function instead of promote headers to get that info back into the query.

Have fun, and Good Luck with it!

PS: you might want to look at this article: http://www.poweredsolutions.co/2014/11/21/combining-data-from-multiple-excel-workbooks-with-power-qu...

[lamorim]

Thank you very much for your help. It worked.

Best Regards