Forum Discussion
Responsive ResidentCreate Date Column from cell in data file
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.
How about filtering that table on [Column1]= "Date Range" ?
Split Column 2 with "to" as delimter then.
6 Replies
- Greg_Deckler
Community Champion
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.
jpt1228Hi 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 SourceBasically, 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.
- ImkeF
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!
