Forum Discussion

CiceroBC's avatar
CiceroBC
Advocate II
6 years ago
Solved

Add row numbers to each text file from a folder

I'm looking for a way to inject a row number when using the folder source optoin in power query (in Excel). I have a large amount of text files with unique and different file extensions based on the ...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi CiceroBC ,

     

    this part is reasonably simple:

    1. Import files from a folder (a case may be from an online folder):

    2. Filter, if necessary, and then click on the button in the "Content" header:

    3. Confirm Ok on the next step. PBI will generate some code to import your data in both the current query and in a separate group. The group will look something like this:

    4. Select the Transform Sample File query and add an index column:

     

    Once it is added in the template query, PBI will update a function linked to this query and once you come back to your original query you should see something like this:

     

     

    Hope this helps :).

     

    This is the code generated by PBI:

    Main query:

    let
    Source = Folder.Files("D:\"),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "123.abc" or [Name] = "123.xyz")),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (2)", each #"Transform File (2)"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (2)"})
in #"Removed Other Columns1"

     

    And the transform function called from the main query (in my case the function is called Transform File (2)😞

    let
    Source = (Parameter2 as binary) => let
        Source = Table.FromColumns({Lines.FromBinary(Parameter2, null, null, 1252)}),
        #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1)
    in
        #"Added Index"
in
    Source

     

    Kind regards,

    JB

     

     

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi CiceroBC ,

 

do you have a reference table similar to:

FileNameSearchInRowValueKeyInRow
123.xyz1MONTH46
123.xyz
1YEAR4
123.abc
12??????

 

And obviously the text files can be parsed using CSV.Document to rows in a way that makes sense?

 

Thanks,

JB

CiceroBC's avatar
CiceroBC
Advocate II
6 years ago

Hi Anonymous,

 

I haven't built out a reference table for the logic as of yet. This issue is a step towards that end.

 

Concerning the CSV.Document command, I'm using the folder as a source because this tool will be to extract values from a few thousand reports in a folder.  To better clarify, here is the M I'm using so far to view the file contents (please note that "Search Folder" is a parameter so this can be tested with a smaller folder of files first).

 

let
Source = Folder.Files(SearchFolder),
#"Filtered Rows" = Table.SelectRows(Source, each ([Extension] <> ".xlsm" and [Extension] <> ".xlsx")),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Report File", each #"Report File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Report File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Report File", Table.ColumnNames(#"Report File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Column1", type text}})
in
#"Changed Type"

 

The end result is two columns of "Source.Name" (the file name) and "Column1" the content of the file, stacked on top of each other. My objective is to establish an row index that numbers each row, and starts over every time there is a new file. Once that is done, I can begin my reference table based on the logic that I've documented so far.

 

Thanks!