Add row numbers to each text file from a folder

[CiceroBC]

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 data in each (even though they are actually all text files). These different files have value that are specific to the row number in each file type based on a value in the first row of each file.

So for example, if file 123.xyz has the word "MONTH" in the first row of text, then I know that the key value is in row 46. If the first row has the word "YEAR" in the first row, then I know that the key value is in row 4. However, if the file is 123.abc, I need to look for the word in row 12, which sends me to different rows for key values, etc...

To build out the logic, I need to know the row number for every file. Essentially, I am looking for an index counter that is nested to start over every time new filename is in the combined output.

I've seen multiple solutions using DAX, but as this is Power Query in Excel (before you ask...yes, it has to be Excel) I really need a solution in M.

[v-frfei-msft]

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]

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.Name	Column1 (file content)	File Row Index
123.xyz	This is a MONTH report (I'll be using the first line to identify the file)	1
123.xyz	extra report information from the program that makes these reports	2
123.xyz	other report information	3
123.xyz	Data I need from this row of the file	4
123.xyz	still some other report information	5
123.abc	This is a YEAR report (FYI there are dozens of these variations)	1
123.abc	some other report information I don't need	2
123.abc	Data I need from this row of this file	3
123.abc	Sometimes I need data from multiple rows depending on the first row	4

Does that help clarify what I am looking for?

Thanks!

[Anonymous · Accepted solution]

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]

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!

[Anonymous]

Hi @CiceroBC ,

do you have a reference table similar to:

FileName	SearchInRow	Value	KeyInRow
123.xyz	1	MONTH	46
123.xyz	1	YEAR	4
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]

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!