Forum Discussion
Handling missing date column: Pulling date from one cell to create a date column in select files
Reposted (unable to continue with initial thread)).
I'm importing data from hundreds of csv files into Power BI and I have a problem that I hope someone can help me resolve. Some of the files have a date column but others don't. Those that don't have a date column also have unwanted top rows (see image). The files that have a date column have a date range stamped somewhere in the file (cell B10). For these files I want to add a date column, grab the date (01/01/2024) and populate the date column with it. After that, I want to remove all unwanted rows (1-17) from similar files but leave all other files intact (those don't need any rows removed). Any suggestions for how to do it in Power Query?
dufoq3 and @Ibendlin you previously responded to my initial question. I revised the post as advised. Thank you!
Here are the links. I'm importing from a folder in C drive to Power BI.
Report 1: https://docs.google.com/spreadsheets/d/1I6MmH2gBzibr6_uuwxQBOTfAMzdgWUGxGzKhxhgwGG0/edit?usp=sharing
Report 2: https://docs.google.com/spreadsheets/d/1xMyDm_5dS9MUxox_UYx1uNNzO3V7GsxcMCVQY7KtLhE/edit?usp=sharing
Expected: https://docs.google.com/spreadsheets/d/1QI-dzcWZCiz115aSBFiAho9gyLxeuzghmDfhiA2L5UI/edit?usp=sharing
So as per my understanding, you wanted to get the sheet ( that has a date range and blank rows) to be transformed.
I have made the trasformations and the table now looks like thisPlease see the file for exact transformation steps ayan2_jpss_sol.pbix
If you find this helpful, Please Consider marking this as a solution.
5 Replies
There is no access to the file, please check it again, additionally you can also share your data or smaple of your data here also
Thank you Jpss and Omid_Motamedise
Here are the links. I'm importing from a folder in C drive to Power BI.
Report 1: https://docs.google.com/spreadsheets/d/1I6MmH2gBzibr6_uuwxQBOTfAMzdgWUGxGzKhxhgwGG0/edit?usp=sharing
Report 2: https://docs.google.com/spreadsheets/d/1xMyDm_5dS9MUxox_UYx1uNNzO3V7GsxcMCVQY7KtLhE/edit?usp=sharing
Expected: https://docs.google.com/spreadsheets/d/1QI-dzcWZCiz115aSBFiAho9gyLxeuzghmDfhiA2L5UI/edit?usp=sharingSo as per my understanding, you wanted to get the sheet ( that has a date range and blank rows) to be transformed.
I have made the trasformations and the table now looks like thisPlease see the file for exact transformation steps ayan2_jpss_sol.pbix
If you find this helpful, Please Consider marking this as a solution.Thank you very much!
