如何在 powerbi 函数中循环遍历列中的元素? [英] How can I loop trough elements in columns in a powerbi function?

问题描述

我想写一个函数来清理数字.将它们作为文本保留.

I want to write a Function for cleaning numbers. While leaving them as text.

所以，在我的查询中我想使用例如:

So, in my queries I want to use e.g.:

= CleanRN(PrevQueryTable, {"NumericTextColumn"})

该函数应该删除所有非数字字符和非数字文本中的前导零.(但函数不是这个问题的重点.)

The function is supposed to remove all non numeric characters and leading zeroes in not already numeric texts. (But the function is not the point for this question.)

所以在查询本身中我可以使用

So in the queries themselves I can go with

= Table.ReplaceValue(Benutzerdefiniert2,"_","",Replacer.ReplaceText,{"ONKZ/RufNr"})

或

= Table.ReplaceValue(#"Ersetzter Wert1", each try Number.From([RUFNUMMER]) otherwise true, each Text.TrimStart(Text.Select([ZMM_onkz], {"0".."9"}),"0")) ,Replacer.ReplaceValue,{"RUFNUMMER"})

或类似的东西.

但我想包装一个函数以实现可重用性和更好的阅读.(以及对M的更好理解)

But I want to wrap in a function for reusability and better reading. (And a better understanding of M)

我的第一次:

(Tbl_InputTable as table, Txt_Column as text) as table => let
    Result = Table.ReplaceValue(
        Tbl_InputTable, 
        each try Number.From(Table.Column(Tbl_InputTable, Txt_Column)) otherwise true, 
        each Text.TrimStart(Text.Select(Table.Column(Tbl_InputTable, Txt_Column), {"0".."9"}),"0"), 
        Replacer.ReplaceValue, 
        {Txt_Column}
    )
in
Result

由于现在已知的原因，这不起作用 [col] 和 Table.Column() 不相等.

This didn't work for the now known reason that [col] and Table.Column() are not equal.

所以我又去了:

= (Tbl_InputTable as table, Lst_Column as list) as table => let
    Result = Table.TransformColumns(
        Tbl_InputTable,
        List.Transform(
            Lst_Column,
            each {_, Text.TrimStart(Text.Select(_, {"0".."9"}), "0")}
        )
    )
in
Result

以

Fehler in der Abfrage ''. Expression.Error: Der Wert """" kann nicht in den Typ "Function" konvertiert werden.

这很好，并不完全是一个惊喜 - 我猜 - 因为 _ 指的是 Lst_Column 的元素.

Which is kinda fine and not exactly a surprise - I assume - as _ is refering to the elements of Lst_Column.

更新:第三次尝试

(Tbl_InputTable as table, Lst_Column as list) as table => let
    Result = Table.TransformColumns(
        Tbl_InputTable,
        List.Transform(
            Lst_Column,
            each {
                _, 
                Table.TransformColumns(
                    Tbl_InputTable, 
                    {
                        _, 
                        each Text.TrimStart(Text.Select(_, {"0".."9"}),"0")
                    }
                )
            }
        )
    )
in
Result

所以，我希望通过将它包装在第二个 Table.TransformColumns 中来更深一层，以便 _ 引用列的元素.但是没有.

So, I hoped by wrapping it in a second Table.TransformColumns to go one layer deeper so that _ refers to the elements of the column. But no.

非描述性错误:

Fehler in der Abfrage ''. Expression.Error: Ein Wert vom Typ "Table" kann nicht in den Typ "Function" konvertiert werden.

<小时>

那么.. 我如何在函数中使用函数?如果我只有列"，如何将列称为 [列]?

---

So.. How do I use functions within functions? How can I refer to a column as [column] if I only have the "column"?

推荐答案

实际上你们已经很接近了.您唯一缺少的是，在您的 List.Transform 中，您希望第二个元素是一个函数，而不是列名中提取的数字.要解决这个问题，只需添加一个 each 以便它告诉如何转换每个值，而不是该值是什么.

You're actually very close. The only bit you are missing is that in your List.Transform you want the second element to be a function, not the extracted numbers in your column name. To fix this, just add an each so it's telling how to transform each value rather than what that value is.

(Tbl_InputTable as table, Lst_Column as list) as table => let
    Result = Table.TransformColumns(
        Tbl_InputTable,
        List.Transform(
            Lst_Column,
            each {_, each Text.TrimStart(Text.Select(_, {"0".."9"}), "0")}
        )
    )
in
Result

定义此函数后，您应该可以像这样调用它:

With this function defined, you should be able to call it like this:

CleanRN(PrevQueryTable, {"NumericTextColumn"})

<小时>

每个 ... 是一个函数，它把 _ 转换成你指定的任何东西(相当于语法 (_) => ...，一个带有参数 (_) => ... 的函数)代码>_).在这种情况下，我们在一个函数中有一个函数.外层是将每个 _ 转换为列表 {_, function}，而内层是指定该函数是什么.我们想要这个列表的原因从 我之前回答过的你链接的问题.

each ... is a function that transforms _ into whatever you specify (equivalent to syntax (_) => ..., a function with parameter _). In this case, we have a function within a function. The outer one is transforming each _ into a list {_, function} and the inner one is specifying what that function is. The reason we want this list is more clear from the question you linked that I answered previously.

这篇关于如何在 powerbi 函数中循环遍历列中的元素?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！