The column is of type number and powerbi interprets it as type date ... HELP!!!!

[oreuglas]

Hello, I have been dealing with this for days and I turn to you as I can not find any solution.

This is the file in case you want to reproduce the error. https://drive.google.com/file/d/105-U2L0CJNyH1MQCsfzpKT5GIi-fW890/view?usp=sharing

1. I import the data. OK!!

2. I put: user first row as headers .OK!!

3. Some columns recognize it correctly as a decimal number, and others as a date, when they are all decimal values. ERROR.

 4. I TRIED TO CHANGE COLUMN TYPE TO DECIMAL NUMBER AND I GET ALL FIELDS AS ERRORS.

Click on Error, I got:

Whatt??????  

Is it a number and is pbi trying to convert it into a date?

I have checked the csv, the values ​​are correctly placed as a number.

I have done everything, but the error persists .. in that column is specific and another one, not being able to advance.

Please Helppppp!!!!!!!

[AlexisOlson · Accepted solution]

I'd recommend removing your duplicated column and unpivoting before assigning data types. Like this:

let
    Source = Csv.Document(File.Contents("C:\Users\aolson\Downloads\pbi_error_numer_data_type.csv")),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Removed Columns" = Table.RemoveColumns(#"Promoted Headers",{"3/1/2021_1"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Topic"}, "Date", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Date", type date}, {"Value", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Value] <> null)
in
    #"Filtered Rows"

[alena2k]

hi! Import looks into first 200 rows and if values are empty picks the date.

Try to following: right mouse click on Query and sselect Advanced editor.
Take entire text in Notepad replace all "type date" with "Int.Type", bring it back to Advanced editor. Click Done.

Correction: take text starting from  #"Changed Type1" = and down, replace and bring it back.

[oreuglas]

What worked was immediately after importing the data, opening the advanced editor, copying and pasting in notepad and changing type date to type text, pasting it back into the advanced editor, then selecting the columns and converting it to a decimal number.

[ryan_mayu]

@oreuglas 

pls check the applied steps. how many changed type do you have?

if you have more than one, that means you change number to date to get error result and change error to decimal number. That's why you still get error. you need to delete the changed type steps and reset the data type.