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

     

     

     

CiceroBC's avatar
CiceroBC
Advocate II
6 years ago

Hi v-frfei-msft,

 

Please accept my apologies for not providing enough specifics. Getting a row count might be helpful in some future steps, but my objective right now is to have a running index of each row in each file - which I'll call a "File Row Index". I'm afraid I can't provide any source files, as it is company proprietary information, but I've tried to approximate what I have in mind below:

  

Source.NameColumn1 (file content)File Row Index
123.xyzThis is a MONTH report (I'll be using the first line to identify the file)1
123.xyzextra report information from the program that makes these reports2
123.xyzother report information3
123.xyzData I need from this row of the file4
123.xyzstill some other report information5
123.abcThis is a YEAR report (FYI there are dozens of these variations)1
123.abcsome other report information I don't need2
123.abcData I need from this row of this file3
123.abcSometimes I need data from multiple rows depending on the first row4

 

Does that help clarify what I am looking for?

Thanks!

Anonymous's avatar
Anonymous
Not applicable
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

 

 

 

  • CiceroBC's avatar
    CiceroBC
    Advocate II
    6 years ago

    Hi Anonymous ,

     

    That's exactly what I was looking for!  It hadn't occured to me to add the index in the sample file level.

     

    Thanks very much!