Forum Discussion

Vinod_P's avatar
Vinod_P
Helper I
2 years ago
Solved

Unable to pull file names in folder

Hi ALL, 

 

I am using Power query to get file names in a folder .

 

Get data --> from file --> from folder 

 

Unfortunately I am having too many file subfiles which is cross 200,000 rows and unable to pull due to capacity issues .

 

Is there a way to enter the folder path and get the files names related to that folder alone either by using power qery or VBA 

 

Thanks 

Need Help
Tips and Tricks
  • slorin's avatar
    slorin
    2 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcraKiQlJzS1Qio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Folder" = _t]),
    FolderContents = Table.AddColumn(Source, "Contents", each Folder.Contents([Folder])),
    Expand = Table.ExpandTableColumn(FolderContents, "Contents", {"Name", "Extension", "Attributes", "Folder Path"}, {"Name", "Extension", "Attributes", "Folder Path"}),
    Kind = Table.ExpandRecordColumn(Expand, "Attributes", {"Kind"}, {"Kind"}),
    MySpecificFolder = Table.AddColumn(Kind, "Specific_Folder", each if [Name]="xxxx" and [Kind]="Folder" 
        then Folder.Contents([Folder Path]&[Name]) else null)
    in
    MySpecificFolder

    Stéphane 

3 Replies

    • Vinod_P's avatar
      Vinod_P
      Helper I
      2 years ago

      Hi Stephane,

       

      Thank you for the quick response. Thats the perfect solution and could you please help me little further. 

       

      From the above I am getting list of folder names and from that will select one and need file names from the specific folder 

  • slorin's avatar
    slorin
    Super User
    2 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcraKiQlJzS1Qio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Folder" = _t]),
    FolderContents = Table.AddColumn(Source, "Contents", each Folder.Contents([Folder])),
    Expand = Table.ExpandTableColumn(FolderContents, "Contents", {"Name", "Extension", "Attributes", "Folder Path"}, {"Name", "Extension", "Attributes", "Folder Path"}),
    Kind = Table.ExpandRecordColumn(Expand, "Attributes", {"Kind"}, {"Kind"}),
    MySpecificFolder = Table.AddColumn(Kind, "Specific_Folder", each if [Name]="xxxx" and [Kind]="Folder" 
        then Folder.Contents([Folder Path]&[Name]) else null)
    in
    MySpecificFolder

    Stéphane