导入数据后替换所有列的所有错误值(同时保留行) [英] Replace all error values of all columns after importing datas (while keeping the rows)

问题描述

作为数据源的Excel表可能包含错误值(#NA，#DIV/0)，这可能会干扰以后在

An Excel table as data source may contain error values (#NA, #DIV/0), which could disturbe later some steps during the transformation process in Power Query.
Depending of the following steps, we may get no output but an error. So how to handle this cases?

我在Power Query中找到了两个标准步骤来捕获它们:

I found two standard steps in Power Query to catch them:

• 删除错误(用户界面:首页/删除行/删除错误)->所有带有错误的行都将被删除
• 替换错误值(用户界面:转换/替换错误)- >首先必须选择要执行此操作的列.

• Remove errors (UI: Home/Remove Rows/Remove Errors) -> all rows with an error will be removed
• Replace error values (UI: Transform/Replace Errors) -> the columns have first to be selected for performing this operations.

第一种可能性不是我的解决方案，因为我想保留行并仅替换错误值.

The first possibility is not a solution for me, since I want to keep the rows and just replace the error values.

就我而言，我的数据表会随着时间而变化，这意味着列名称可能会更改(例如，年份)，或者会出现新的列.因此，第二种可能性太静态了，因为我不想每次都更改脚本.

In my case, my data table will change over the time, means the column name may change (e.g. years), or new columns appear. So the second possibility is too static, since I do not want to change the script each time.

因此，我尝试了一种动态的方式来清理所有列，这些列与列名(和列数)无关.它将错误替换为空值.

So I've tried to get a dynamic way to clean all columns, indepent from the column names (and number of columns). It replaces the errors by a null value.

let
    Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],

    //Remove errors of all columns of the data source. ColumnName doesn't play any role
    Cols = Table.ColumnNames(Source),
    ColumnListWithParameter = Table.FromColumns({Cols, List.Repeat({""}, List.Count(Cols))}, {"ColName" as text, "ErrorHandling" as text}),
    ParameterList = Table.ToRows(ColumnListWithParameter ),
    ReplaceErrorSource = Table.ReplaceErrorValues(Source, ParameterList)
in
    ReplaceErrorSource

在将两个新列(带有错误)添加到源之后，这是三个不同的查询消息:

Here the different three queries messages, after I've added two new column (with errors) to the source:

如果有人有其他解决方案可以进行这种数据清理，请在此处写您的帖子.

If anybody has another solution to make this kind of data cleaning, please write your post here.

推荐答案

let
    src = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    cols = Table.ColumnNames(src),
    replace = Table.ReplaceErrorValues(src, List.Transform(cols, each {_, "!"}))
in
    replace

这篇关于导入数据后替换所有列的所有错误值(同时保留行)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！