Import data from a folder with different fileformats

To expand a bit what Wyn suggested.

Create two function, one to get csv file

let
    fnGetCSV = (FilePath as text) => let
    Source = Csv.Document(File.Contents(FilePath),
        [Delimiter=",", Columns=2, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    PromotHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
    in
        PromotHeaders
in
    fnGetCSV

and another for Excel file

let
    fnGetExcel = (FilePath as text) => let
    Source = Excel.Workbook(File.Contents(FilePath), null, true),
    GetFirstSheet = Table.SelectRows(Source, each [Kind] = "Sheet"){0}[Data],
    PromotHeaders = Table.PromoteHeaders(GetFirstSheet, [PromoteAllScalars=true])
    in
        PromotHeaders
in
    fnGetExcel

Here we always take first sheet of the file (you could change that).

After that use connector to the folder with your files and edit the query.  Remove all columns but these ones

and add custom column with

remove all but this new column and expand it. After that cosmetic if necessary

Resulting script is

let
    Source = Folder.Files(pMyFolder),
    RemoveUnused = Table.SelectColumns(Source,{"Name", "Extension", "Folder Path"}),
    AddFunctions = Table.AddColumn(RemoveUnused, "GetFile", each if [Extension] = ".xlsx"
        then fnGetExcel([Folder Path]&[Name])
        else fnGetCSV([Folder Path]&[Name])),
    KeepTables = Table.SelectColumns(AddFunctions,{"GetFile"}),
    ExpandTable = Table.ExpandTableColumn(KeepTables, "GetFile", {"Car", "Date"}, {"Car", "Date"}),
    ChangeType = Table.TransformColumnTypes(ExpandTable,{{"Car", type text}, {"Date", type date}})
in
    ChangeType

and attached.