Forum Discussion

Jeff_Huber_1967's avatar
Jeff_Huber_1967
Frequent Visitor
3 years ago
Solved

Using the file name as data in a column

Hello,

I am new to Power BI.

I have a txt file that I imported in into Power BI. 

The file name is Balance - 2021 10 02.txt

I would like to be able to automatically take the date part of the file name and use that to create a column called Report Date and then take the right 10 characters of the file name and transform that into a date automatically and populate the Report Date column with that data. The report date should be in the MM/DD/YYYY format so the report date for this file should be 10/02/2021.

 

Thanks,

Jeff

Need Help
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    3 years ago

    Hi Jeff_Huber_1967 ,

     

    You may try to use invoke function to do that via a query like the following:

    let
    Source = (a as binary, b as date) =>

    let 
        Content = a,
        #"Imported Excel Workbook" = Excel.Workbook(Content),
        Sheet1_Sheet = #"Imported Excel Workbook"{[Item="Sheet1",Kind="Sheet"]}[Data],
        #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
        #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"type", type text}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each b)
    in
        #"Added Custom"
in
    Source

     

    Before using this query, you need get the date in the file's name.

     

     

    Pbix in the end you can refer.

    Best Regards

    Community Support Team _ chenwu zhu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Community Support
    3 years ago

    Hi Jeff_Huber_1967 ,

     

    You may try to use invoke function to do that via a query like the following:

    let
    Source = (a as binary, b as date) =>

    let 
        Content = a,
        #"Imported Excel Workbook" = Excel.Workbook(Content),
        Sheet1_Sheet = #"Imported Excel Workbook"{[Item="Sheet1",Kind="Sheet"]}[Data],
        #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
        #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"type", type text}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each b)
    in
        #"Added Custom"
in
    Source

     

    Before using this query, you need get the date in the file's name.

     

     

    Pbix in the end you can refer.

    Best Regards

    Community Support Team _ chenwu zhu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.