Remove all errors with Power Query

[Arnault_]

Dear All,

My data source is folder containing 3 csv files.

When I tried to update it, I found out there were few rows with errors.

I would like to make sure these errors are properly removed. i don't want to do that column by column but remove all errors in one step.

Here is my code (partially) :

let
    Source = Folder.Files("C:XXXXX"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from Intl_orders", each #"Transform File from Intl_orders"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from Intl_orders"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Intl_orders", Table.ColumnNames(#"Transform File from Intl_orders"(#"Sample File"))),
   #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"order_oc_id", type text}, {"customer_id", type text}, {"project_id", type text}, {"country_id", type text}, {"supplier_id", type text}, {"order_priority_type", type text}, {"order_id", type text}, {"product_id", type text}, {"product_name", type text}, {"product_type", type text}, {"product_group", type text}, {"product_family", type text}, {"product_cat", type text}, {"packing_id", type text}, {"shipment_id", type text}, {"dispath_address", type text}, {"mode_transport", type text}, {"quantity_ordered", Int64.Type}, {"quantity_packed", Int64.Type}, {"TS01_stock_take_date", type date}, {"TS02_creation_date", type date}, {"TS03_validation_date", type date}, {"TS04_confirmation_date", type date}, {"TS05_packing_date", type date}, {"TS06_req_green_light_date", type date}, {"TS07_green_light_date", type date}, {"TS08_shipment_date", type date}, {"TS09_transp_reception_date", type date}, {"TS10_order_reception_date", type date}, {"order_status", type text}, {"invoice_id", type text}, {"invoice_date", type date}, {"volume", type number}, {"weight", type number}, {"invoiced_amount", type number}, {"estimated_delivery_date", type date}, {"req_delivery_date", type date}, {"std_delivery_date", type date}, {"conf_delivery_date", type date}, {"actual_delivery_date", type date}}),

   (...)

    #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows1",{{"quantity_ordered", type number}, {"quantity_packed", type number}}),
    #"Filtered Rows2" = Table.SelectRows(#"Changed Type1", each true),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows2", "order_line_id", each [order_id]&"/"&[product_id]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "packing_line_id", each if [packing_id] = "" then "" else [packing_id]&"/"&[product_id]),
    #"Filtered Rows3" = Table.SelectRows(#"Added Custom1", each [TS04_confirmation_date] >= #date(2016, 1, 1)),
    #"Added Custom2" = Table.AddColumn(#"Filtered Rows3", "order_completion", each if [TS10_order_reception_date] = null then "In progress" else "Complete")
in
    #"Added Custom2"

How can I do that? Where should I put the following code (if the right one)?

Table.ReplaceErrorValues

Thanks in advance for your help.

[v-frfei-msft · Accepted solution]

Hi @Arnault_,

Assume you want to remove  the error  of the columns  "order_line_id", "quantity_ordered", "packing_id". Then we can edit the code as below. 

let
    Source = Folder.Files("C:XXXXX"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from Intl_orders", each #"Transform File from Intl_orders"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from Intl_orders"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Intl_orders", Table.ColumnNames(#"Transform File from Intl_orders"(#"Sample File"))),
   #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"order_oc_id", type text}, {"customer_id", type text}, {"project_id", type text}, {"country_id", type text}, {"supplier_id", type text}, {"order_priority_type", type text}, {"order_id", type text}, {"product_id", type text}, {"product_name", type text}, {"product_type", type text}, {"product_group", type text}, {"product_family", type text}, {"product_cat", type text}, {"packing_id", type text}, {"shipment_id", type text}, {"dispath_address", type text}, {"mode_transport", type text}, {"quantity_ordered", Int64.Type}, {"quantity_packed", Int64.Type}, {"TS01_stock_take_date", type date}, {"TS02_creation_date", type date}, {"TS03_validation_date", type date}, {"TS04_confirmation_date", type date}, {"TS05_packing_date", type date}, {"TS06_req_green_light_date", type date}, {"TS07_green_light_date", type date}, {"TS08_shipment_date", type date}, {"TS09_transp_reception_date", type date}, {"TS10_order_reception_date", type date}, {"order_status", type text}, {"invoice_id", type text}, {"invoice_date", type date}, {"volume", type number}, {"weight", type number}, {"invoiced_amount", type number}, {"estimated_delivery_date", type date}, {"req_delivery_date", type date}, {"std_delivery_date", type date}, {"conf_delivery_date", type date}, {"actual_delivery_date", type date}}),

   (...)

    #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows1",{{"quantity_ordered", type number}, {"quantity_packed", type number}}),
    #"Filtered Rows2" = Table.SelectRows(#"Changed Type1", each true),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows2", "order_line_id", each [order_id]&"/"&[product_id]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "packing_line_id", each if [packing_id] = "" then "" else [packing_id]&"/"&[product_id]),
    #"Filtered Rows3" = Table.SelectRows(#"Added Custom1", each [TS04_confirmation_date] >= #date(2016, 1, 1)),
    #"Added Custom2" = Table.AddColumn(#"Filtered Rows3", "order_completion", each if [TS10_order_reception_date] = null then "In progress" else "Complete"),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Added Custom2", {"order_line_id", "quantity_ordered", "packing_id"})
in
    #"Removed Errors"

Also this could be done by UI just CTRL+left click all the columns that you want to removed the errors and choose the Remove errors icon.

If it doesn't meet your requirement, kindly share your sample data and excepted result to me. Please upload your files to One Drive and share the link here.

Regards,

Frank

[Arnault_]

Hi @v-frfei-msft,

I have chosen the solution you proposed. I was actuallty looking for something a bit different.

I found an other solution here.

Thanks for your support.