Forum Discussion

Cofinia's avatar
Cofinia
Frequent Visitor
10 years ago

Retain xlsx file name in new column when using Folder as a data source

Hi,

I'm using a folder to import files to PowerBI.

I used the information given on this forum to solve the problem of importing xlsx files

http://exceleratorbi.com.au/combine-excel-workbooks-power-query-method-2/>

 

My files are monthly tial balance that I saved as 20160131.xlsx, 20160229.xlsx, etc

I would like to add a new column to have the date retrieved from the file name.

 

I saw other way with csv files, tried it, but it's not working.

 

Thank you for your help

 

2 Replies

  • IvanBond's avatar
    IvanBond
    Icon for Advocate II rankAdvocate II
    10 years ago

    Hello,

     

    when you query From Folder, you have column [Name] in query editor. Left to cut it on pieces with help of functions Text.Start, Text.End, Text.Range.

    formula for "20160229.xlsx"

    #date( Number.From( Text.Start( [Name], 4    ) ), 
           Number.From( Text.Range( [Name], 4, 2 ) ), 
           Number.From( Text.Range( [Name], 6, 2 ) ) 
    )

    BR,

    Ivan

    • Cofinia's avatar
      Cofinia
      Frequent Visitor
      10 years ago

      Hi IvanBond,

      I tried your solution, but I was not able to make it work.

      I selected the 8 first digits ans it worked.

      Then I change the format to dates.

      Thank you for your help.