Dataflow refresh fails due to conversion error

[Anonymous]

Goodmorning,

I've been trying to refresh my dataflow but it keeps failing due to this error:
Error: DataFormat.Error: We couldn't convert to Number. Request ID: d6e40ed0-e74a-820b-7fe0-aa3794b80d14 Activity ID: 86abf8b4-7c59-48c2-bc6d-256e40b38b42

The problem is that i don't have any number columns. I went over my advanced editor numerous times, as well als the steps i do and i don't understand why is error keeps popping up. I'm combining two csv files from a sharepoint folder, to one table and this usually works fine, but for some reason it keeps giving the error when i refresh. Not when validating the querries...

I've copied most of the query below and underlined the Change Type steps. This is the main query, i've also checked the helper query and i also 'force'all types to text in that one. The first steps are left out due to company urls that I prefer not to share.

Query:

#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered rows 1", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (2)", each #"Transform File (2)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (2)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (2)", Table.ColumnNames(#"Transform File (2)"(#"Sample File (2)"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"u_business_service", type text}, {"u_business_service.u_managed_by_group", type text}, {"u_business_service.used_for", type text}, {"u_application_server", type text}, {"u_application_server.u_managed_by_group", type text}, {"u_application_server.sys_class_name", type text}, {"u_application_server.version", type text}, {"u_application", type text}, {"u_application.u_managed_by_group", type text}, {"u_application.used_for", type text}, {"u_database_instance", type text}, {"u_database_instance.sys_class_name", type text}, {"u_database_instance.u_managed_by_group", type text}, {"u_database_instance.used_for", type text}, {"u_database_instance.u_server", type text}, {"u_database_instance.version", type text}, {"u_database_catalog", type text}, {"u_virtual_machine", type text}, {"u_virtual_machine.support_group", type text}, {"u_windows_linux_server", type text}, {"u_windows_linux_server.support_group", type text}, {"u_windows_linux_server.os", type text}, {"u_windows_linux_server.os_version", type text}, {"u_application_server.operational_status", type text}, {"u_database_instance.operational_status", type text}, {"u_windows_linux_server.operational_status", type text}}),
#"Renamed columns 1" = Table.RenameColumns(#"Changed Type", {{"u_business_service", "Business Service"}, {"u_business_service.u_managed_by_group", "BS_Man_by"}, {"u_business_service.used_for", "BS_used_for"}, {"u_application_server", "Application Server"}, {"u_application_server.u_managed_by_group", "AS_Man_by"}, {"u_application_server.sys_class_name", "AS_Class"}, {"u_application_server.version", "AS_version"}, {"u_application", "Web/Application"}, {"u_application.u_managed_by_group", "WA_Man_by"}, {"u_application.used_for", "WA_used_for"}, {"u_database_instance", "Database Instance"}, {"u_database_instance.sys_class_name", "DB_Class"}, {"u_database_instance.u_managed_by_group", "DB_Man_by"}, {"u_database_instance.used_for", "DB_used_for"}, {"u_database_instance.u_server", "DB_Server"}, {"u_database_instance.version", "DB_Version"}, {"u_database_catalog", "DB_Catalog"}, {"u_virtual_machine", "Virtual Machine"}, {"u_virtual_machine.support_group", "VM_Sup_gr"}, {"u_windows_linux_server", "Windows/Linux Server"}, {"u_windows_linux_server.support_group", "WLS_Sup_gr"}, {"u_windows_linux_server.os", "WLS_OS"}, {"u_windows_linux_server.os_version", "WLS_Version"}}),
#"Added custom" = Table.AddColumn(#"Renamed columns 1", "WLS_OS+Version", each [WLS_OS] & " " & [WLS_Version]),
#"Reordered columns" = Table.ReorderColumns(#"Added custom", {"Business Service", "BS_Man_by", "BS_used_for", "Application Server", "AS_Man_by", "AS_Class", "AS_version", "Web/Application", "WA_Man_by", "WA_used_for", "Database Instance", "DB_Class", "DB_Man_by", "DB_used_for", "DB_Server", "DB_Version", "DB_Catalog", "Virtual Machine", "VM_Sup_gr", "Windows/Linux Server", "WLS_Sup_gr", "WLS_OS", "WLS_Version", "WLS_OS+Version", "u_application_server.operational_status", "u_database_instance.operational_status", "u_windows_linux_server.operational_status"}),
#"Inserted conditional column" = Table.AddColumn(#"Reordered columns", "BU", each if Text.StartsWith([WA_Man_by], "BE") then "NN BE" else if Text.StartsWith([WA_Man_by], "CZ") then "NN CZ" else if Text.StartsWith([WA_Man_by], "NL CFG AAV") then "AAV" else if Text.StartsWith([WA_Man_by], "NL CFG BANK") then "NN Bank" else if Text.StartsWith([WA_Man_by], "NL CFG C&C") then "NN C&C" else if Text.StartsWith([WA_Man_by], "NL CFG CIO/CSH") then "CIO CSH" else if Text.StartsWith([WA_Man_by], "NL CFG CIO/ETS") then "CIO ETS" else if Text.StartsWith([WA_Man_by], "NL CFG CIO/IIS") then "CIO IIS" else if Text.StartsWith([WA_Man_by], "NL CFG CIO/PRO") then "CIO" else if Text.StartsWith([WA_Man_by], "NL CFG CIO/SD") then "CIO SD" else if Text.StartsWith([WA_Man_by], "NL CFG CSH") then "CIO CSH" else if Text.StartsWith([WA_Man_by], "NL CFG FSC") then "NN CFO" else if Text.StartsWith([WA_Man_by], "NL CFG HOF") then "CIO HOF" else if Text.StartsWith([WA_Man_by], "NL CFG IIO") then "NN CFO" else if Text.StartsWith([WA_Man_by], "NL CFG ITCC") then "NN EUP" else if Text.StartsWith([WA_Man_by], "NL CFG LEVEN") then "NN Life" else if Text.StartsWith([WA_Man_by], "NL CFG LP") then "NN Life" else if Text.StartsWith([WA_Man_by], "NL CFG OHRA") then "NN Non-Life" else if Text.StartsWith([WA_Man_by], "NL CFG SI") then "NN Non-Life" else if Text.StartsWith([WA_Man_by], "ES ") then "NN ES" else null),
#"Changed column type 2" = Table.TransformColumnTypes(#"Inserted conditional column", {{"BU", type text}, {"WLS_OS+Version", type text}}),
#"Duplicated column" = Table.DuplicateColumn(#"Changed column type 2", "WA_Man_by", "WA_Man_by - Copy"),
#"Split column by delimiter" = Table.SplitColumn(#"Duplicated column", "WA_Man_by - Copy", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Country", "CFG", "WA_Man_by - Copy.3"}),
#"Split column by position" = Table.SplitColumn(#"Split column by delimiter", "Source.Name", Splitter.SplitTextByPositions({0, 10}, false), {"Date", "Source.Name.2"}),
#"Changed column type 1" = Table.TransformColumnTypes(#"Split column by position", {{"Date", type text}, {"Source.Name.2", type text}, {"Country", type text}, {"CFG", type text}, {"WA_Man_by - Copy.3", type text}})
in
#"Changed column type 1"

Now i know the query is rather big, but i hope someone can tell me what is happening here. It is quite frankly driving me a little crazy 🙂

Kind regards,

Odi

[jeffshieldsdev]

You should be able to...I just connected to a folder New Folder and it generated the following.  Add steps you want applied to each file (provided they have the same structure) to the "Transform Sample File" step...probably after Promoted Headers step.

[Anonymous]

Hi Jeff,

Yes I tried adjusting the helper queries, but not with succes. If in the first screen you get after you click on "combine files" you select "Based on first 200 rows" your data type detection already determines it is a number column, and uses that to make the helper querries. However my number column has text somewhere after row 200 and thus it fails. The only thing that worked for me is to use "Do not detect datatypes" as shown in picture two. But then for every file it adds to your table you get a row simmilar to row 1 in the second picture. Not ideal, but easy to filter out.

If you still think it should be possible i might try it again, but with validating my flow and refreshing it, it is very time consuming to do this trial and error wise.

Kind regards,

Luca

[jeffshieldsdev]

Can't you edit the Changed Type step or whatever in the helper query?  Change the step to treat u_windows_linux_server.os_version as Text?

[jeffshieldsdev]

What steps are in your "#"Transform File (2)"" query before all these other ones though? Anything casting of data types there?

[Anonymous]

Hi Jeff,

I finally found out why it didn't work. It was indeed in the helper querries. The only way i've now managed to prevent the dataflow from giving an error is by not automatically letting it determine the data type (based on the first 200 rows). One downside of this is that it doesn't combine the CSV files under proper column headers anymore, so i had to filter out the rows of the added csv files that contain headers. 
To the best of my knowledge it is thus not possible to 'hard code' the data type in those automated steps, which is unfortunate. The workaround does work luckily 🙂