Power Query Import error - Data in moving columns due to blanks

[RooBoyAust]

Hi Team,

I have been using power query for a few months & this week it decided to corrupt on some new reports, moving data across columns due to blanks in some fields. Originally this was not an issue, but not i cannot find how to set the Null before importing & am exhausted for ideas.

Appreciate any input on this.

Drew

 

[sandy666]

I don't understand what are you tryin' to do but...

Null = blank
0 = 0
"" = empty string

all above isn't the same

 

[RooBoyAust]

Hi Sandy, Apologies.

The data has blank fields, which I would say is Null for sure.

The data comes from a csv string from a web-based maintenance system we use. So often there are fields with no data, which have caused some to migrate across, causing data to corrupt.

Being csv format, i guess it may have an issue distinguishing the fields. But I dont understand why it happened, aside from the latest 365 update this week possibly causing a change.

 

[sandy666]

Without example I can't say too much.
So after load csv to PQ, check data format of columns and if there are numbers use Replace Values from null to 0. (probably)
CSV is text file so if PQ doesn't convert format automatically - do that manually
Format of columns = Any is not a good idea

 

[RooBoyAust]

Dead right! Hard part is, I cannot set the data types before the import, therefore the data is corrupt before I can change it.

I appreciate its difficult without an example, but as usual its sensitive info, so best i don't use it publicly.

 

[sandy666]

After import to PQ, probably you see on the right side (steps):

• Source
• Changed Type

so remove/delete second step and try set type of data manually
Columns should have proper format (not mixed). It's up to you which is text and which is a number
if column contain text values set whole column as text or transform table as you need then set format again

 

[sandy666]

One more thing...
after load csv into PQ check for the first error then check the value in this place in csv file edited in notepad and post this value here

 

[peter789]

Can you check the structure of the csv file before connecting? If it is truly comma delimited blank values should appear as ,,. If not the problem could be with the source. The other possibility is that the file is delimited in another way, tabs are often used as delimiters instead of commas. Another way to check is to connect to just one of the csv's with Power Query. You should then get a preview dialog, you can then adjust the default settings if the results are not as expected.

 

[RooBoyAust]

Can you check the structure of the csv file before connecting? If it is truly comma delimited blank values should appear as ,,. If not the problem could be with the source. The other possibility is that the file is delimited in another way, tabs are often used as delimiters instead of commas. Another way to check is to connect to just one of the csv's with Power Query. You should then get a preview dialog, you can then adjust the default settings if the results are not as expected.

Thanks, Peter & Sandy,

It turned out to be me changing the text to numbers before save & load causing the issue. Must have caused a conflict with cells with no data from that point.

Appreciate both of your assistance.

Drew

 

[sandy666]

you are welcome

have a nice day