CODEWITHSUNDEEP
excelfunctionpowerbipowerquery
A Cohen
A Cohen

Reputation: 456

Power Query File Count function

I am trying to come up with a small function that will update my main query. There is one line in my code that is currently static, and I would like it to update as I add files to my folder. The number is based off the files in the folder, 1 - "Files in the Folder". Currently there are 14 files in my folder, so the line looks like so:

= Table.RenameColumns(#"Added LAST_FIRST_MID NAME",{{"1", "Report Count"}, {"-13", "Index"}})

So, the bold number needs to be a function of 1 - Count(Files in Folder). The problem lies in the fact that I have no idea how to create a function from scratch. I tried creating a file with the following code and creating a function from that table.

let
    Source = Folder.Files("P:\CREDIT DEPT\Credit Bureau - Analysis\BOSP CC DataFiles"),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, -1)
in
    #"Added Index"

FileCount Table

This is my first attempt at trying to create a function, I think that I'm on the right path, but I'm not sure where to go from here. Thanks in advance for your help.

Upvotes: 1

Views: 1074

Answers (1)

horseyride
horseyride

Reputation: 21318

If you are trying to count number of files and then use that variable, try below

Use Home...Advanced Editor... to paste in the first row above where you intend to reference the result

FileCount = Text.From(1 - Table.RowCount(Folder.Files("P:\CREDIT DEPT\Credit Bureau - Analysis\BOSP CC DataFiles"))),
#"Rename" = Table.RenameColumns(#"Added LAST_FIRST_MID NAME",{{"1", "Report Count"}, {FileCount, "Index"}})

Upvotes: 1

Related Questions