Forum Discussion
Help with Data transformation
I have exported data from. csv files to Power BI. One of the columns contains text values ( product description) and is getting dispersed across other columns. It looks the same in excel as well. Is there anyway I can transform or shape it, so that the product description fits in one single column? In this example the first column contains product description while the second column contains time. Further, I have also noticed that Power BI shows that the data type of opt is text when it should be date time. This is probably due to the dispersion of text. I'm confused about how to proceed further and put the product description in a single column. Please kindly help. Thank you!
Example :
Column names - Product Description opt
Row Values - Motor Single22:11
Gear Box
4 Replies
Hi Anonymous
Could you please upload one of your files to OneDrive, Google Drive or Dropbox then provide a link to it. I don't knwo if the data you provided above is accurately formatted. As I have to copy and paste it into a text file, there's a risk the formatting is lost.
As it is, that file is not a CSV. CSV stands for comma separated variable and the columns in that file are not comma separated. They may be trying to use tab separated columns but when I try to import it, it's messed up, because there are tabs between text in fields (like the description) as well as between columns. Power Query is splitting it up where it sees a tab.
So, if that is an accurate copy of one of your files, the process by which the are exported and created is not really working. The export of the data needs to be changed so you get files you can work with, preferably proper CSV files.
Regards
Phil
Hi Anonymous
If your source data is getting split across columns incorrectly then there's either something wrong with the format of the source CSV, or the import of the dfata is incorrect, or both.
Can you please share a sample CSV file?
Regards
Phil
Thanks for your prompt reply. Here is sample data that represents how the data is stored. Please kindly let me know how I could rectify this issue with the csv file. Thank you !
ProductDescription OnHoldCode OpTime$ PlannedOpQty Workshop ScheduleLocked Priority Complete ProjectCode DateCompleted StatusCode EarliestStartDate ResourceCode ProductGroup DateRequired QuantityInStock ------------------ ---------- ------- ------------ -------- -------------- -------- -------- ----------- ------------- ---------- ----------------- ------------ ------------ ------------ --------------- MT MOTOR PLATES ASSEMBLY (RH) 00:00:00 40 GL No 0 No AM NULL 5 NULL SUB NDT 00:00.0 3 PL WING 00:00:00 44 GL No 0 No NULL 7 NULL SUB NDT 00:00.0 0 CH ASSEMBLY + CP564565 00:00:00 3 SM No 0 No NULL 7 NULL SUB NDT 00:00.0 0 BU - Concession 3000012013 & 5000016448 00:00:00 50 GL No 0 No NULL 7 NULL SUB NDT 00:00.0 32 MM Housing 00:00:00 8 GL No 0 No NULL 7 NULL SUB PLT 00:00.0 174 CNG LINK RIGID SAU 00:00:00 30 GL No 0 No NULL 7 NULL SUB NDT 00:00.0 6 PIN CLEVIS LINKAGE 00:00:00 2 SM No 0 No NULL 7 NULL SUB HT 00:00.0 0
Hi, Anonymous
Use the navigator to correctly import the data of your CSV file. If it still cannot be imported correctly, then you need to modify the data of your CSV file.
refer:
Connect to CSV files in Power BI Desktop
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
