Help with Data transformation

[Anonymous]

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

[PhilipTreacy]

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

[v-angzheng-msft]

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

Import CSV file to Power BI

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.

[PhilipTreacy]

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

[Anonymous]

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