将Excel公式转换为Power查询

Question

在Power中，我有一个列，它显示某些项的不同持续时间，但它在试图按时间或持续时间进行转换时会显示一个错误。

作为Excel表旁边的一个解决方案，我创建了一个公式，用于以我希望使用的格式来转换持续时间，但我无法将公式转换为Power Query可以理解的语言(我对Power Query非常陌生)。

这是如何从源中提取数据的：

​

​

，但我喜欢这样展示：

​

​

我用来完成这个任务的Excel公式是：

=IF(LEN([@Age])=7,"0"&[@Age],IF(LEN([@Age])=5,"00:"&[@Age],IF(LEN([@Age])=4,"00:0"&[@Age],IF(LEN([@Age])=3,"00:00"&[@Age],[@Age]))))

在Power查询中而不是Excel表中使用它将是很好的，因为它是一个学习平台。

我是自我学习能力查询在Excel，所以任何帮助都欢迎。

编辑:如果持续时间超过24:00:00，我将如何处理它？

​

​

这是它返回的错误代码

​

​

Answer 1

可以添加带有公式的自定义列：

Duration.FromText(
            Text.Combine(
                List.LastN(
                    {"00"} & List.ReplaceValue(Text.Split([Age],":"),"","00",Replacer.ReplaceValue),
                        3),
            ":"))

公式

element

• Retrieve

• 通过冒号将文本字符串拆分为list

• ，用{00}替换空格，并将列表的前三个元素(最后三个元素)加上一个冒号分隔的文本字符串。

• 使用Duration.FromText函数将列的数据类型转换为duration

在PQ中，持续时间将具有d.hh:mm:ss格式，但当您将其加载回Excel中时，可以将其更改为[hh]:mm:ss

您可以在PQ用户界面中完成上述所有操作。

下面是做同样事情的M-Code：

let
    Source = Excel.CurrentWorkbook(){[Name="Table16"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Age", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Duration", each Duration.FromText(
            Text.Combine(
                List.LastN(
                    {"00"} & List.ReplaceValue(Text.Split([Age],":"),"","00",Replacer.ReplaceValue),
                        3),
            ":"))),
            
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Age"})
in
    #"Removed Columns"

您甚至可以通过使用Table.TransformColumns函数(使用高级编辑器中的M代码)来完成该操作，而无需添加列：

let
    Source = Excel.CurrentWorkbook(){[Name="Table16"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Age", type text}}),
 
    #"Change to Duration" = Table.TransformColumns(#"Changed Type",
        {"Age", each Duration.FromText(
            Text.Combine(
                List.LastN(
                    {"00"} & List.ReplaceValue(Text.Split(_,":"),"","00",Replacer.ReplaceValue),
                        3),
            ":")), type duration})
in
    #"Change to Duration"

所有的结果都是：

​

​

编辑

使用修改后的数据，现在显示超过23个小时的持续时间值(PQ中的工期文本中不允许)，转换将有所不同。如果超过23小时，我们必须检查时间，并将其分成几天和几个小时。

注意:下面的编辑还假设在day 位置中永远不会输入任何内容；并且条目的分钟和秒将始终在范围内。如果可能存在值，则只需向小时条目中的“溢出”添加内容即可

因此，我们更改自定义列公式以检查：

let 
            split = List.LastN({"00","00"} & List.ReplaceValue(Text.Split([Age],":"),"","00",Replacer.ReplaceValue),4),
            s = Number.From(List.Last(split)),
            m = Number.From(List.LastN(split,2){0}),
            hTotal = Number.From(List.LastN(split,3){0}),
            h = Number.Mod(hTotal,24),
            d = Number.IntegerDivide(hTotal,24)
        in #duration(d,h,m,s)

​

​

如果您可能有几分钟或几秒钟的非法值，则可以添加logig以检查该值。

此外，如果要将其加载到Excel中，并且总天数可能超过31天，则需要将其格式化(在d.hh:mm:ss中)，因为d.hh:mm:ss Excel的格式不能显示超过31天(尽管适当的值将存储在单元格中)。