Search code examples
excelpowerquerym

Editting source from a query that loads a folder of CSV files. Any ideas on what "{0}[Content]" means?

I have a small test workbook where I was testing and learning PowerQuery. I happened to change its folder location and that messed up all my queries. I edited them all and changed the source as to solve the problem but that did not work for the query data imported data from a folder of CSV files. That particular query generates a subfolder of other 4 queries. I managed to solve it by checking the code (in the advanced editor) from a backup. I noticed that some part was changed (by the system) and by adding it to one of the "system" queries, everything worked. I am trying to understand why that one line is important and I have .

So, I checked some quick microsoft references as to check what the FOlder.Files function does but that did not clear what {0} or [Content] mean.

Summarizing the piece of code that I am trying to understand is:

"Arquivo de Amostra" query (translates to Sample File)

let
    Fonte = Folder.Files("C:\SomePath\Transactions"),
    Navegação1 = Fonte{0}[Content]
in
    Navegação1

and it can be translated to:

let
    Source = Folder.Files("C:\SomePath\Transactions"),
    Navigation1 = Source{0}[Content]
in
    Navigation1

Why is that line so important and why the query did not work before "reincluding" it? What does the {0} mean? I do think that somehow that links the content to the return of the query.

There are other 3 queries which do not mention this parameter, though they reference the query "Arquivo de amostra" I can include them too, but I am trying to avoid excessive or unnecessary info.

I'll also include the original query that loads data from a CSV folder.

let
    Fonte = Folder.Files("C:\SomePath\Transactions"),
    #"Arquivos Ocultos Filtrados1" = Table.SelectRows(Fonte, each [Attributes]?[Hidden]? <> true),
    #"Invocar Função Personalizada1" = Table.AddColumn(#"Arquivos Ocultos Filtrados1", "Transformar Arquivo de Transactions_1997-1998", each #"Transformar Arquivo de Transactions_1997-1998"([Content])),
    #"Colunas Renomeadas1" = Table.RenameColumns(#"Invocar Função Personalizada1", {"Name", "Nome da Origem"}),
    #"Outras Colunas Removidas1" = Table.SelectColumns(#"Colunas Renomeadas1", {"Nome da Origem", "Transformar Arquivo de Transactions_1997-1998"}),
    #"Coluna de Tabela Expandida1" = Table.ExpandTableColumn(#"Outras Colunas Removidas1", "Transformar Arquivo de Transactions_1997-1998", Table.ColumnNames(#"Transformar Arquivo de Transactions_1997-1998"(#"Arquivo de Amostra"))),
    #"Tipo Alterado" = Table.TransformColumnTypes(#"Coluna de Tabela Expandida1",{{"Nome da Origem", type text}, {"transaction_date", type date}, {"stock_date", type date}, {"product_id", Int64.Type}, {"customer_id", Int64.Type}, {"store_id", Int64.Type}, {"quantity", Int64.Type}}),
    #"Linhas Classificadas" = Table.Sort(#"Tipo Alterado",{{"transaction_date", Order.Ascending}}),
    #"Colunas Removidas" = Table.RemoveColumns(#"Linhas Classificadas",{"Nome da Origem"})
in
    #"Colunas Removidas"
Solution

  • When you load a folder, you get a table in the following format:

    Content | Name  | Extension | Date accessed      | [...etc...]
--------+-------+-----------+--------------------+-------------
Binary  | FileA | .csv      | 7/10/2019 12:25 PM | [...etc...]
Binary  | FileB | .xlsx     | 7/10/2019 10:25 AM | [...etc...]
...     | ...   | ...       | ...                | ...

    This is the Source step. In the Navigation step, the {0} refers to the first row of this table (it's indexed starting at 0) and [Content] refers to the first column that contains the binary data for that file.

    It's like an Excel A1 reference but row/column instead of column/row.

    You could eliminate that step by loading a file directly rather than navigating from a folder path. For example, instead of 

    Source = Folder.Files("C:\SomePath\Transactions"),
Navigation1 = Source{0}[Content]

    you could write

    Source = Csv.Document(File.Contents("C:\SomePath\FileA.csv"))

    assuming FileA.csv is the file in the first row from the folder.