Transform files from folder before combining?

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

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>