Very big file size of .csv/.txt files to convert to Excel data

• Is is normal for the file sizes to be this big?
  For Log file. It can get quite large. I often deal with log for PBX system with conferencing bridge and these can be quite large.
• Is there an easier way to do this conversion on a daily basis?
  Depends... It will be easier for us to help you. If you have small file that's representative of your .csv (about 50 to 100 rows of data to test things). You can upload csv in zipped file.
• Will Power Query work if I load these files into a Data model (The aim is to use the data on a weekly/monthly basis to create 20 different graphs that show shows either weekly or monthly comparisons)
  Yes, data model can handle millions of rows of data quite comfortably, but it will depend on your Excel version. 2010 will have issue, as data can't be directly loaded from PQ to Data model.
  2013 or later has new engine and is more efficient, but isn't compatible with 2010 version of data model.

EDIT: Oh if you need file as xlsx format and with data in sheet, Data model is not the best method as it does not load data to sheet (though you can use PowerView/PowerPivot to analyse and report on data).

Then I would set it up like below.

1. Designate a folder where txt/csv file would be stored.

2. Using Get & Transform, query the folder.

3. If only the latest file is needed, add variable 'LatestFile' in Advanced editor and use that as filter condition on Source[Date modified] column.

4. Remove all but the [Content] column.

5. Expand the column using custom delimiter (pipe character).

6. Change column data type as needed and load to data model.

Sample M code: Change Filter.Files() as needed.

let
    Source = Folder.Files("C:\Test\Sample_Simone\StockFile"),
    LatestFile = List.Max(Source[Date modified]),
    #"Filtered Rows" = Table.SelectRows(Source, each [Date modified] = LatestFile),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Removed Columns", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source", type text}, {"SupplierCode", type text}, {"SupplierDesc", type text}, {"BranchCode", Int64.Type}, {"BranchDesc", type text}, {"Brand", type text}, {"ReportingDivCode", Int64.Type}, {"ReportingDivDesc", type text}, {"ReportingRegCode", Int64.Type}, {"ReportingRegDesc", type text}, {"DepartmentCode", Int64.Type}, {"DepartmentDesc", type text}, {"SubDepartmentCode", Int64.Type}, {"SubDepartmentDesc", type text}, {"CategoryCode", Int64.Type}, {"CategoryDesc", type text}, {"SubCategoryCode", Int64.Type}, {"SubCategoryDesc", type text}, {"ItemCode", Int64.Type}, {"ItemDesc", type text}, {"ProductBrand", type text}, {"BarcodeCode", Int64.Type}, {"DateLastOrdered", Int64.Type}, {"DateLastReceipted", Int64.Type}, {"QtyLastOrdered", Int64.Type}, {"QtyLastReceipted", Int64.Type}, {"DCStockQty", Int64.Type}, {"RateOfSale", type number}, {"StockQuantity", Int64.Type}, {"StockValue", type number}, {"SellPrice", type number}, {"DateLastSold", Int64.Type}, {"DateLastUpdated", Int64.Type}, {"OnRangeIndicator", type text}, {"OnPromotion", type text}})
in
    #"Changed Type"