Create Date Column from cell in data file

[jpt1228]

Hello, I have a monthly data file that I want to import into my model. The file needs to reference the date for the data. The file name does not have a date so I need to take the beginning part of the date from Column2, Row 9 and move it into a column.

The column would be called 'Date' and would contain 01/01/2015 for every row there is data. 

[ImkeF · Accepted solution]

How about filtering that table on [Column1]= "Date Range" ?

Split Column 2 with "to" as delimter then.

[jpt1228]

I am sure the previous solution would have worked but this looks much simpler. I wonder if this would be something useful as a feature. I don't know how often this would need to be done.

Thanks!

[jpt1228]

Hi @Greg_Deckler I am using a folder query however, the files do not have any date reference in the file name. They are all named: Sales Data.xls. The only date reference is in the actual file in that one cell. I also cannot use the file date stamp because the file may not get saves in the folder until the following month. Any other ideas?

[Greg_Deckler]

So, what I was thinking and trying to convey is that you could write an M Function that you would pass in the contents of file as a table. You would then parse that to the correct cell and return the date value as the output of your function. You would use that function in a custom column in the query and thus all of the rows of your query would have the correct date stamp.

[Greg_Deckler]

Take a look at this example function.

let
    Source = (#"FN Sample File Parameter1" as text) => let
        Source = Excel.Workbook(File.Contents(#"FN Sample File Parameter1"), null, true),
        Table3_Table = Source{[Item="Table3",Kind="Table"]}[Data],
    #"Replaced Errors" = Table.ReplaceErrorValues(Table3_Table, {{"Metric", 0}, {"Frequency", 0}, {"ACTUAL", 0}, {"BUDGET", 0}, {"PRIOR YEAR", 0}, {" ", 0}, {"BUDGET1", 0}, {"PRIOR YEAR1", 0}, {" 2", 0}, {"ACTUAL2", 0}, {"BUDGET2", 0}, {"PRIOR YEAR2", 0}, {" 3", 0}, {"BUDGET3", 0}, {"PRIOR YEAR3", 0}}),
    #"Removed Columns" = Table.RemoveColumns(#"Replaced Errors",{"Frequency", " ", " 2", " 3"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Metric", type text}, {"ACTUAL", type number}, {"BUDGET", type number}, {"PRIOR YEAR", type number}, {"BUDGET1", type number}, {"PRIOR YEAR1", type number}, {"ACTUAL2", type number}, {"BUDGET2", type number}, {"PRIOR YEAR2", type number}, {"BUDGET3", type number}, {"PRIOR YEAR3", type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"BUDGET1", "CURRENT STATUS vs. BUDGET"}, {"PRIOR YEAR1", "CURRENT STATUS vs. PRIOR YEAR"}, {"ACTUAL2", "YTD ACTUAL"}, {"BUDGET2", "YTD BUDGET"}, {"PRIOR YEAR2", "YTD PRIOR YEAR"}, {"BUDGET3", "YTD vs. BUDGET"}, {"PRIOR YEAR3", "YTD vs. PRIOR YEAR"}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Metric"}, "Attribute", "Value"),
    #"Merged Columns" = Table.CombineColumns(#"Unpivoted Other Columns",{"Metric", "Attribute"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Transposed Table" = Table.Transpose(#"Merged Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
        #"Kept Range of Rows" = Table.Range(Table3_Table,0,1),
        #"Removed Other Columns" = Table.SelectColumns(#"Kept Range of Rows",{"Sales $ ACTUAL"})
    in
        #"Removed Other Columns"
in
    Source

Basically, what this is doing is returning just a single row, single column value from an Excel spreadsheet that you feed the path in via the parameter. You should have that path, or you could just get rid of the File.Contents if you have the binary object. You can run it through a series of transformations and then just return back the value that you want. You would then call this function as part of a custom column.

Would probably be quite a bit of overhead but should work.

[Greg_Deckler]

Are you using a Folder query? I would look at perhaps creating a Function to grab this and then integrating that into your overall query for importing each file.