Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Currently combining multiple excel docs with the date in the name of the excel file
eg: 2018 Capex Projects - February Forcast.xlsx
Would like to make a column that can be used to identify the date from the above example as 02/2018, is there a funtion that i can use that will help do this?
Solved! Go to Solution.
@Anonymous,
Create the following columns in your table.
Month = if(search("january",[Source.Name],1,0)>0,01,if(search("february",[Source.Name],1,0)>0,02,if(search("march",[Source.Name],1,0)>0,03,if(search("april",[Source.Name],1,0)>0,04,if(search("may",[Source.Name],1,0)>0,05,if(search("june",[Source.Name],1,0)>0,06,if(search("july",[Source.Name],1,0)>0,07,if(search("august",[Source.Name],1,0)>0,08,if(search("september",[Source.Name],1,0)>0,09,if(search("october",[Source.Name],1,0)>0,10,if(search("november",[Source.Name],1,0)>0,11,if(search("December",[Source.Name],1,0)>0,12))))))))))))
Year = LEFT([Source.Name],4)
YearMonth = [Month]&"/"&[Year]
Regards,
Lydia
@Anonymous,
Create the following columns in your table.
Month = if(search("january",[Source.Name],1,0)>0,01,if(search("february",[Source.Name],1,0)>0,02,if(search("march",[Source.Name],1,0)>0,03,if(search("april",[Source.Name],1,0)>0,04,if(search("may",[Source.Name],1,0)>0,05,if(search("june",[Source.Name],1,0)>0,06,if(search("july",[Source.Name],1,0)>0,07,if(search("august",[Source.Name],1,0)>0,08,if(search("september",[Source.Name],1,0)>0,09,if(search("october",[Source.Name],1,0)>0,10,if(search("november",[Source.Name],1,0)>0,11,if(search("December",[Source.Name],1,0)>0,12))))))))))))
Year = LEFT([Source.Name],4)
YearMonth = [Month]&"/"&[Year]
Regards,
Lydia
in excel, i know this might be a cluncky formula, but i would use something like this to identify the month:
if(isnumber(search("january",[Source.Name])),01,null)if(isnumber(search("february",[Source.Name])),02,if(isnumber(search("march",[Source.Name])),03,if(isnumber(search("april",[Source.Name])),04,if(isnumber(search("may",[Source.Name])),05,if(isnumber(search("june",[Source.Name])),06,if(isnumber(search("july",[Source.Name])),07,if(isnumber(search("august",[Source.Name])),08,if(isnumber(search("september",[Source.Name])),09,if(isnumber(search("october",[Source.Name])),10,if(isnumber(search("november",[Source.Name])),11,if(isnumber(search("december",[Source.Name])),12,NULL))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.