CODEWITHSUNDEEP
excelpowerquery
GuidoT
GuidoT

Reputation: 312

Load multiple csv from table into PowerQuery

I have an Excel-Sheet with a table, containing a list of URLs pointing to a couple of CSV-files.

These CSVs all have the same structure but contain various different data.

I would like to load these CSVs with a PowerQuery-function but I somehow seem to miss something here because both options I was trying return errors.

let
    #"CSVPaths" = Excel.CurrentWorkbook(){[Name="CSVPaths"]}[Content][Column1],
    #"FromCSVDocs" = Csv.Document(each #"CSVPaths",[Delimiter=";", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"FromFileContents" = Files.Contents(each #"CSVPaths")
in
    #"FromFileContents"

I was hoping to get a list of tables that I simply may transform and expand after.

Upvotes: 0

Views: 65

Answers (1)

GuidoT
GuidoT

Reputation: 312

I solved the problem with a workaround through a List.Generate-loop:

    let
        #"CSVPaths" = Excel.CurrentWorkbook(){[Name="CSVPaths"]}[Content][Column1],
        #"PathCount" = List.Count(#"CSVPaths"),

        #"CSVList" = List.Generate
        (
            ()=>0,
            each _<#"PathCount",
            each _+1,
            each Table.PromoteHeaders
            (
                Csv.Document(File.Contents(#"CSVPaths"{_}),[Delimiter=";", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None])
            )
        ),
        #"CSVTables" = Table.FromColumns({#"CSVList"},{"CSVTables"}),
        #"Expanded CSVTables" = Table.ExpandTableColumn(#"CSVTables", "CSVTables", {"Col1", "Col2"}, {"Col1", "Col2"})
    in
        #"Expanded CSVTables"

Upvotes: 1

Related Questions