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

     

     

     

v-frfei-msft's avatar
v-frfei-msft
Community Support
6 years ago

Hi CiceroBC ,

 

Group by should help you.

 

Then we can duplicate the table and merge.

 

 

If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

 

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!