Changes to file before loading

[efilipe]

Hi guys!

I'm having a problem on my file. We have mora than 2000 lines of content but, 13 of them, has html code in it. Importing is giving me 13 errors. I'd like to be able make a change on the text before inserting the values in cells. Is that possible?

We're doing this manually, everyday. 😞

Thanks!

[stretcharm]

Do you get the errors on type convertion?

Try replace the values.

If you still get the error ensure your replace is before the error. You can reorder the steps by clicking and selecting move up or down

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKk5JLFaK1YlWSoSzTE2MTcCM4pS0lLR0MNPYzLQ4pSgNzI4pNTAwTk4qAtOpJanFJcUQIX2YGNSYlLTi9AygmbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [data = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"<br>","",Replacer.ReplaceText,{"data"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","</br>","",Replacer.ReplaceText,{"data"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value1",{{"data", type text}})
in
    #"Changed Type"

If this fails you can also replace errors if you just want to avoid the error.

[efilipe]

let
Source = Folder.Files("D:\"),
#"D:\track csv" = Source{[#"Folder Path"="D:\",Name="tracks.csv"]}[Content],
#"Imported CSV" = Csv.Document(#"D:\tracks csv",[Delimiter=",", Columns=62, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"id", type text}, {"autoSlug", type text}, {"name", type text}, {"tags", type text}, {"status", type text}, {"countAllTimeVideosWatched", type number}, {"countAllTimeVideosWatched50p", type number}, {"countAllTimeEventsPlayerFavoritar", Int64.Type}, {"countAllTimeEventsPlayerMenuDownloadClick", Int64.Type}, {"countAllTimeEventsTelaCompartilhar", Int64.Type}, {"nameMultiline", type text}, {"description", type text}, {"type", type text}, {"notes", type text}, {"state", type text}, {"publishedAt", type datetime}, {"isReady", type logical}, {"isPublished", type logical}, {"submittedAt", type datetime}, {"typedWho", type text}, {"typedWhere", type text}, {"typedSport", type text}, {"typedManeuver", type text}, {"typedProducer", type text}, {"typedElement", type text}, {"typedWhat", type text}, {"typedSoundtrack", type text}, {"typedMission", type text}, {"typedMood", type text}, {"typedFlag", type text}, {"permanentSlug", type text}, {"relativeURL", type text}, {"absoluteURL", type text}, {"shortlink", type text}, {"shortlinkId", type text}, {"shortlinkAbsoluteUrl", type text}, {"metadataTitle", type text}, {"metadataDescription", type text}, {"positionBoost", Int64.Type}, {"cachedDefaultPlaylistRelativeURL", type text}, {"cachedDefaultPlaylistAbsoluteURL", type text}, {"cachedDurationMs", type number}, {"cachedTransformationURLHls", type text}, {"cachedTransformationURLMpegDash", type text}, {"cachedTransformationURL360p", type text}, {"cachedTransformationURL480p", type text}, {"cachedTransformationURL720p", type text}, {"cachedTransformationURL1080p", type text}, {"cachedTransformationURLStories", type text}, {"analyserLastCheckedAt", type datetime}, {"analyserCheckedCount", type number}, {"createdAt", type datetime}, {"updatedAt", type datetime}, {"isDeleted", type logical}, {"deletedAt", type text}, {"toBeDeleted", type logical}, {"createdBy", type text}, {"createdById", type text}, {"submittedBy", type text}, {"submittedById", type text}, {"submission", type text}, {"submissionId", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","Novidades OFF, ","",Replacer.ReplaceText,{"typedWhat"})
in
#"Replaced Value"

[stretcharm]

Try Deleting the step "Changed Type" and remove any problem data.

Then you can the type convertion back using Transform - Detect Data Types or by setting each column.

If this doesn't work provide me a few rows (including a bad row) from the tracks file.

[v-frfei-msft]

Hi @efilipe,

Please to turn the option off and import again.

Regards,

Frank

[efilipe]

Hey @v-frfei-msft, it was already activated. Thanks for the support

[v-frfei-msft]

Hi @efilipe,

I mean to make it inactive.

Regards,

Frank

[v-frfei-msft]

Hi @efilipe,

Has your issue been solved? If any other question, feel free to let me know please.

Regards,

Frank

[efilipe]

Yes, because since is has html code, it's importing those 13 fields to the wrong columns. it's like having a line break in the middle of the file 13 times.