Forum Discussion
Folder.Files not combining all files in the folder
Hi All,
While trying to combine files from within a folder as follows :
While all the files within the folder are getting pulled here, when I am expanding the [Custom]column to combine the table files, the data appended are only till the May and not beyond that...
The M code from the advanced editor is as below :
let
Source = Folder.Files("C:\Users\mmchak\OneDrive - Merisant\Documents\sales data\ecomm\amazon\MTD"),
#"Added Custom" = Table.AddColumn(Source, "Custom", each Excel.Workbook([Content])),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name.1", "Data", "Item", "Kind", "Hidden"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Hidden] = false)),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Name", "Name.1", "Data"}),
#"Added Custom1" = Table.AddColumn(#"Removed Other Columns", "Custom", each Table.PromoteHeaders([Data])),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom1",{"Custom", "Name", "Name.1", "Data"}),
TableSource = Table.RemoveColumns(#"Reordered Columns",{"Data"}),
#"Expanded Custom1" = Table.ExpandTableColumn(TableSource, "Custom", {"Amazon Order Id", "Merchant Order Id", "Shipment ID", "Shipment Item Id", "Amazon Order Item Id", "Merchant Order Item Id", "Purchase Date", "Payments Date", "Shipment Date", "Reporting Date", "Buyer Email", "Buyer Name", "Buyer Phone Number", "Merchant SKU", "Title", "Shipped Quantity", "Currency", "Item Price", "Item Tax", "Ship Service Level", "Recipient Name", "Shipping Address 1", "Shipping Address 2", "Shipping Address 3", "Shipping City", "Shipping State", "Shipping Postal Code", "Shipping Country Code", "Shipping Phone Number", "Billing Address 1", "Billing Address 2", "Billing Address 3", "Billing City", "Billing State", "bill-postal-code", "bill-country", "Carrier", "Tracking Number", "Estimated Arrival Date", "FC", "Fulfillment Channel", "Sales Channel", "Item Promo Discount", "Shipment Promo Discount"}, {"Amazon Order Id", "Merchant Order Id", "Shipment ID", "Shipment Item Id", "Amazon Order Item Id", "Merchant Order Item Id", "Purchase Date", "Payments Date", "Shipment Date", "Reporting Date", "Buyer Email", "Buyer Name", "Buyer Phone Number", "Merchant SKU", "Title", "Shipped Quantity", "Currency", "Item Price", "Item Tax", "Ship Service Level", "Recipient Name", "Shipping Address 1", "Shipping Address 2", "Shipping Address 3", "Shipping City", "Shipping State", "Shipping Postal Code", "Shipping Country Code", "Shipping Phone Number", "Billing Address 1", "Billing Address 2", "Billing Address 3", "Billing City", "Billing State", "bill-postal-code", "bill-country", "Carrier", "Tracking Number", "Estimated Arrival Date", "FC", "Fulfillment Channel", "Sales Channel", "Item Promo Discount", "Shipment Promo Discount"})
in
#"Expanded Custom1"Is there something I am missing?
Any help will be much appreciated
Hi monojchakrab,
You are not seemingly ding something wrong with the code and it should run from what I can see at least after testing it on my dataset I can't see anything obviously wrong in the code).
The only obvious exception that can cause what you see (missing Jun & onwards) would be that the files are actually empty (as if there data was there, but the column names were different, PQ would fetch a stack of nulls that you would be able to see).
Can you try to filter out the files that are loading from the ones that ain't and run the code on the ones that don't want to load to see if this change their behaviour (of course check that they are not empty first).
Cheers,
John
Thanks jbwtp - I will try that.
The files are not empty actually, but somehow it worked it out itself - I tried a different hack as I noticed that in the files column, all the files were not loaded and selected before loading to BI. Once I did that and split the date-time into date & time separately, all the files loaded correctly.
But thanks for the leg-up.
2 Replies
Hi monojchakrab,
You are not seemingly ding something wrong with the code and it should run from what I can see at least after testing it on my dataset I can't see anything obviously wrong in the code).
The only obvious exception that can cause what you see (missing Jun & onwards) would be that the files are actually empty (as if there data was there, but the column names were different, PQ would fetch a stack of nulls that you would be able to see).
Can you try to filter out the files that are loading from the ones that ain't and run the code on the ones that don't want to load to see if this change their behaviour (of course check that they are not empty first).
Cheers,
John
Thanks jbwtp - I will try that.
The files are not empty actually, but somehow it worked it out itself - I tried a different hack as I noticed that in the files column, all the files were not loaded and selected before loading to BI. Once I did that and split the date-time into date & time separately, all the files loaded correctly.
But thanks for the leg-up.