Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)?
Solved! Go to 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. 🙂
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
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>
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
Hi @Clara,
Does that make sense? If so, kindly mark my answer as a solution to close the case.
Regards,
Frank
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. 🙂
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?
I had this question too but I was able to resolve it after I saw this thread
you can adjust the sample file to how you want it to import into your actual query
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |