Forum Discussion
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 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.
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 SourceKind regards,
JB
6 Replies
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
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!
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.
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!
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 SourceKind regards,
JB
