Transform files from folder before combining?

[Clara]

Hello! I'm a starting user and I feel like I've reached a dead end here. (I've searched for a solution everywhere and haven't found any, sorry if I missed something obvious!)

I have about 250 files stored in 30 separate folders (one for each store unit) inside one main folder named 'Reports'. Each unit would have multiple Excel (.xlsx) files for Sales, Inventory, Products and so on. Right now I'm extracting queries from my 'Reports' directory and getting something like the image below.

My problem is that I would need to merge the tables by report (one large query for Sales including all units, another for Inventory, etc.) BUT I would like to apply transformations to each table BEFORE combining them, given that they are not yet appropriately formatted for merging.

Is there any way I can do that in Power BI (ideally applying the same transformation steps to all filtered queries at once)? 

[tsibilski]

For me that simple solution of transforming the sample file and expecting that all files from the folder will be transformed that way didn't work.

After clearing the data I had in the first column I had Date, Units and Orders, Date, Unit, Orders and so on..

In the other 7 columns I had dates (in the Date row) and values. I needed all the data in 3 columns: Date, Orders and Units. 

Solution

1. Create an index column starting from 0.
2. Then all columns with values (except name and Index) were unpivoted.
3. In the Attribute column a word 'Column' was removed and the Attribute column was changed to number.
4. Then new column needed to be added with a formula: [Attribute]*10000 + [Index].
5. Then all was sorted by the new column.
6. After removing Index, Attribute and new column needed to be added: Date: if [Column1] = "Date" then [Value] else null.
7. Then Type of Value and Date changed to text.
8. Function Fill Down was applied to a Date column.
9. Two further columns were added, Orders: if [Column1] = "Allocation Shortfall (Orders)" then [Value] else 0, Units: [Column1] = "Allocation Shortfall (Units)" then [Value] else 0.
10. From Column1 all rows with the word 'Date' were removed.
11. Then all columns other than Date, Orders and Units were removed.
12. Types were changed to date and number.
13. Finally, Group function was applied to the Date column with Units - Sum of units, Orders - Sum of orders.
    

If you have a similar issue and found a simpler solution, it would be much appreciated if you could share it. 

Thre is the code: 

    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Added Index", {"Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8"}, "Attribute", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Only Selected Columns","Column","",Replacer.ReplaceText,{"Attribute"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Attribute", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each [Attribute]*10000 + [Index]),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Custom", Order.Ascending}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"Index", "Attribute", "Custom"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns1", "Date", each if [Column1] = "Date" then [Value] else null),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom1",{{"Column1", type text}, {"Value", type text}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Date"}),
    #"Added Custom2" = Table.AddColumn(#"Filled Down", "Orders", each if [Column1] = "Allocation Shortfall (Orders)" then [Value] else 0),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Units", each if [Column1] = "Allocation Shortfall (Units)" then [Value] else 0),
    #"Filtered Rows4" = Table.SelectRows(#"Added Custom3", each ([Column1] <> "Date")),
    #"Removed Columns2" = Table.RemoveColumns(#"Filtered Rows4",{"Column1", "Value"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns2",{{"Orders", Int64.Type}, {"Units", Int64.Type}, {"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type2", {"Date"}, {{"Orders", each List.Sum([Orders]), type any}, {"Units", each List.Sum([Units]), type any}}),
    #"Sorted Rows1" = Table.Sort(#"Grouped Rows",{{"Date", Order.Descending}})
in
    #"Sorted Rows1"</li-code>

[v-frfei-msft]

Hi @Clara,

Actually it is not possbile to apply the same transformation steps to all filtered queries at once. However you can duplicate the query and edit the steps accrodingly.

Regards,

Frank

[v-frfei-msft]

Hi @Clara,

Does that make sense? If so, kindly mark my answer as a solution to close the case.

Regards,

Frank

[Clara · Accepted solution]

Thanks, Frank! Unfortunately that wouldn't work for me because, as I said, I have hundreds of files to transform and combine. I did manage to achieve what I needed through a combination of these solutions, though:

Combine CSVs from Folder - Separate Function

Re: Retain file name column when using Folder as a data source - Power Query M

So I'm gonna leave them here in case they would help anyone else. 🙂

[lpetty421]

thank your for asking that question.  i have the same need, to transform before combining.  but i was not able to figure out how to do it using the links you shared.  any advice?

[Anonymous]

I had this question too but I was able to resolve it after I saw this thread

https://community.powerbi.com/t5/Desktop/Delete-top-rows-from-multiple-imported-files-at-once-and-co...

you can adjust the sample file to how you want it to import into your actual query