Data Imported from Web ---NOT able to convert to Number or Text or any format -To remove errors

[murthysydney]

Hi All,

I have data extracted from the web which I am trying to change the data type to Text or Number or any other format to remove errors. I cannot delete it as it contains data. Tried to use clean, trim and then trying to convert still getting errors for few of the rows which have multiple values
I have cleaned and Trim and split columns to extract the numbers but still getting errors when I load and close. Can you suggest any other options?
Thanks

 

[sandy666]

there is no source data
but try this for column13

then remove second column (probably column13 (1) or column13.1 ) and check data type of column13

if Line Feed doesn't work try with Carriage Return or even Carriage Return and Line Feed

 

[sandy666]

Just for fun test
disconnect from the internet (wifi, network cable) and open Power Query Editor then you'll see what we see

 

[murthysydney]

Tried all the ways did not work.. need a different solution looks like.

 

[sandy666]

without source data I can do nothing, sorry
have a nice day

 

[sandy666]

or try

 

[murthysydney]

Hi

I tried it did not work, so tried to clean and remove white spaces. Now the below pic shows how its showing.

Capture3.PNG

drive.google.com

 

[sandy666]

in the column13 is original data? with spaces between numbers or not?
if with spaces - split by space
if without spaces - split by number of characters (5)
as I said before: without source data I can do NOTHING

btw. could you use Excel not PBI ?

 

[sandy666]

try to adapt this:

let
    Source = Web.Page(Web.Contents("https://cmweb.amsys-prod.railapps.sydneytrains.nsw.gov.au/Default.aspx?Group=3&Status=1&DateFrom=29/12/2018&DateTo=29/12/2019")),
    Data5 = Source{5}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", type text}, {"Column4", type date}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", Int64.Type}, {"Column13", type text}, {"Column14", Int64.Type}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column1", "Column16", "Column17", "Column18", "Column19"}),
    Number = Table.TransformColumnTypes(Table.AddColumn(#"Removed Columns", "Custom", each Text.Select([Column13],{"0".."9"})),{{"Custom", Int64.Type}}),
    Result = Table.RenameColumns(Table.ReorderColumns(Table.SelectColumns(Number,{"Column13"}),{"Column2", "Column12", "Custom", "Column14", "Column15"}),{{"Custom", "Column13"}})
in
    Result

not tested of course ?

 

[sandy666]

correction

try to adapt this:

let
    Source = Web.Page(Web.Contents("https://cmweb.amsys-prod.railapps.sydneytrains.nsw.gov.au/Default.aspx?Group=3&Status=1&DateFrom=29/12/2018&DateTo=29/12/2019")),
    Data5 = Source{5}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", type text}, {"Column4", type date}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", Int64.Type}, {"Column13", type text}, {"Column14", Int64.Type}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column1", "Column16", "Column17", "Column18", "Column19"}),
    Number = Table.TransformColumnTypes(Table.AddColumn(#"Removed Columns", "Custom", each Text.Select([Column13],{"0".."9"})),{{"Custom", Int64.Type}}),
    Result = Table.RenameColumns(Table.ReorderColumns(Table.RemoveColumns(Number,{"Column13"}),{"Column2", "Column12", "Custom", "Column14", "Column15"}),{{"Custom", "Column13"}})
in
    Result

not tested of course ?

 

[murthysydney]

Hi Team,

Still getting the error could not figure out the problem below is the editor. Please have a look.


let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs1Lz8xLTS3KzEtXiCk1MDAyU/BNzMwrSc1LzEtOVdJRMlaK1YlW8sxLK0osLikqTS4pLUqFqQzISQSrMbQAKwquTMlLrVTwTS0pygeKGpmBRUPc/ILDQVxDMNc52AXEMQJz3PzAHFOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Division " = _t, Link = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Division ", type text}, {"Link", Int64.Type}}),
#"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "Table 5", each #"Table 5"([Link])),
#"Expanded Table 1" = Table.ExpandTableColumn(#"Invoked Custom Function", "Table 5", {"Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16"}, {"Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16"}),
#"Cleaned Text3" = Table.TransformColumns(Table.TransformColumnTypes(#"Expanded Table 1", {{"Column13", type text}}, "en-AU"),{{"Column13", Text.Clean, type text}}),
#"Trimmed Text1" = Table.TransformColumns(#"Cleaned Text3",{{"Column12", Text.Trim, type text}, {"Column13", Text.Trim, type text}}),



#"Changed Type1" = Table.TransformColumnTypes(#"Trimmed Text1",{{"Column12", type text}}),



#"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 1, 1),
#"Cleaned Text2" = Table.TransformColumns(Table.TransformColumnTypes(#"Added Index", {{"Column13", type text}}, "en-AU"),{{"Column13", Text.Clean, type text}}),
#"Sorted Rows" = Table.Sort(#"Cleaned Text2",{{"Index", Order.Descending}}),
#"Invoked Custom Function1" = Table.AddColumn(#"Sorted Rows", "Comments", each FxCommentsget([Column2])),
#"Cleaned Text" = Table.TransformColumns(Table.TransformColumnTypes(#"Invoked Custom Function1", {{"Column12", type text}}, "en-AU"),{{"Column12", Text.Clean, type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Cleaned Text",{{"Column12", Text.Trim, type text}}),
#"Cleaned Text1" = Table.TransformColumns(Table.TransformColumnTypes(#"Trimmed Text", {{"Column13", type text}}, "en-AU"),{{"Column13", Text.Clean, type text}}),
#"Added Custom" = Table.AddColumn(#"Cleaned Text1", "Custom", each try([Comments])),
#"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"HasError", "Value"}, {"HasError", "Value"}),
#"Expanded Value" = Table.ExpandTableColumn(#"Expanded Custom", "Value", {"Header", "Assigned By", "Action", "Action Date", "Updated By", "Determination", "Determination Date", "Conditions / Scope / Comments"}, {"Header", "Assigned By", "Action", "Action Date", "Updated By", "Determination", "Determination Date", "Conditions / Scope / Comments"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Value", each true),
#"Changed Type3" = Table.TransformColumnTypes(#"Filtered Rows",{{"Column3", type text}, {"Column4", type date}, {"Column5", type date}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type3","","NIL NO DATA",Replacer.ReplaceValue,{"Column10"}),
#"Filtered Rows1" = Table.SelectRows(#"Replaced Value", each true),
#"Changed Type2" = Table.TransformColumnTypes(#"Filtered Rows1",{{"Column12", type number}})
in
#"Changed Type2"