如何在 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.

<小时>

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

---

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屋！