Different source file formats

[adambc]

I have a combination of VBA and M code to select a source file and pass the FilePath/FileName to Power Query but …

Sometimes the source file I’m given is CSV format, other times it’s XLSX format (don’t ask!) …

Is there some M code that can do an if/then/else based on the source file extension that will tell PQ what type of source file to expect?

Thanks …

 

[Matt Allington]

It depends how you want to do it. You can look at

try query otherwise query2

 

[adambc]

It depends how you want to do it. You can look at

try query otherwise query2

@Matt Allington

I could do that in the VBA, but I would need to maintain two queries ...

I was hoping that I could do something like this (FilePath, FileName & FileType are named cells in my Workbook - FilePath & FileName work perfectly) ...

let
   FilePath = Excel.CurrentWorkbook(){[Name="FilePath"]}[Content]{0}[Column1],
    FileName = Excel.CurrentWorkbook(){[Name="FileName"]}[Content]{0}[Column1],
    FilePathName = FilePath & FileName,
    FileType = Excel.CurrentWorkbook(){[Name="FileType"]}[Content]{0}[Column1],
    // THIS IS WHAT I'D LIKE TO DO, BUT IT DOESN'T WORK AS IS!!!
    // START
    If FileType = "csv" then
    Source = Csv.Document(Web.Contents(FilePathName),[Delimiter=",", Columns=20, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    else
    Source = Excel.Workbook(Web.Contents(FilePathName), null, true),
    SourceTable = Table.SelectRows(Source, each [Kind] = "Sheet"){0}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(SourceTable, [PromoteAllScalars=true]),
    // END
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{Table.ColumnNames(#"Promoted Headers"){0}, type date}, {Table.ColumnNames(#"Promoted Headers"){1}, type text}, {Table.ColumnNames(#"Promoted Headers"){2}, type text}, {Table.ColumnNames(#"Promoted Headers"){3}, type text}, {Table.ColumnNames(#"Promoted Headers"){4}, type text}, {Table.ColumnNames(#"Promoted Headers"){5}, type text}, {Table.ColumnNames(#"Promoted Headers"){6}, type text}, {Table.ColumnNames(#"Promoted Headers"){7}, type text}, {Table.ColumnNames(#"Promoted Headers"){8}, type text}, {Table.ColumnNames(#"Promoted Headers"){9}, type date}, {Table.ColumnNames(#"Promoted Headers"){10}, type text}, {Table.ColumnNames(#"Promoted Headers"){11}, type text}, {Table.ColumnNames(#"Promoted Headers"){12}, Int64.Type}, {Table.ColumnNames(#"Promoted Headers"){13}, type text}, {Table.ColumnNames(#"Promoted Headers"){14}, type text}, {Table.ColumnNames(#"Promoted Headers"){15}, type text}, {Table.ColumnNames(#"Promoted Headers"){16}, type text}, {Table.ColumnNames(#"Promoted Headers"){17}, type date}, {Table.ColumnNames(#"Promoted Headers"){18}, Int64.Type}, {Table.ColumnNames(#"Promoted Headers"){19}, type text}})
in
    #"Changed Type"

Or am I hoping for too much?

 

[Matt Allington]

I couldn't say if the above approach can work without seeing the errors. I don't see any issue in having 2 staging queries, one for each file type as I described earlier. If you want to pursue the above if/then approach for learning reasons, then fair enough, but you will have to focus on the error messages rather than simply "it doesn't work at all"

 

[adambc]

I couldn't say if the above approach can work without seeing the errors. I don't see any issue in having 2 staging queries, one for each file type as I described earlier. If you want to pursue the above if/then approach for learning reasons, then fair enough, but you will have to focus on the error messages rather than simply "it doesn't work at all"

@Matt Allington

I really don't want 2 queries that are essentially the same other than the source type!

I will explore why my pseudo IF/THEN/ELSE above code doesn't work when I have some time, but for now, I've implemented my query for .xlsx extracts which I've also mandated (and just in case I'm still sent a .csv extract, I've written some VBA code to detect the file type and if it's .csv, Open the file and perform a Save As .xlsx)

 

[Matt Allington]

I can't see why that can't work. I think the issue you will find is that the if/then must work for both possible outcomes. If one is an error, it will fail. I could be wrong. that is why try/otherwise may be a better option