在Power BI和Excel中的Power Query中查找所有表中的最小日期和最大日期

在Power BI和Excel中的Power Query中查找所有表中的最小日期和最大日期

当我们谈论Power BI中的数据分析时, 日期 表是不可避免的。有多种方法可以创建 日期 table either 在 达克斯 or 在 功率查询。在 达克斯 you my use either CALENDAR() function 要么 CALENDARAUTO() 创建的功能 日期 table. In 功率查询 您可以结合使用 List.Dates()#date() and #duration() 职能。无论哪种方式,总有一个挑战,那就是如何找到一个合适的日期范围,该范围从过去的日期开始,到将来的日期结束,涵盖数据模型中的所有相关日期。一个简单的答案是, we can ask the business. The SMEs know what the valid 日期 range is..

尽管这是一个正确的论点,但并非总是如此。特别是 开始日期 which is a 日期 在 the past. In many cases the business 说:

让我们看一下数据以找出答案.

这也是正确的一点,我们总是可以查看数据,找到其中的所有列 日期 or 约会时间 然后,数据类型将数据按升序或降序排序以获取结果。但是,如果有很多呢?然后,此过程可能非常耗时。

你们中许多人可能已经认为我们可以使用 CALENDARAUTO() in 达克斯 而且我们很乐意去。好吧,那不是很正确。在很多情况下 日期 or 约会时间 我们不能考虑的列 日期 尺寸。如出生日期或去世日期。在这篇文章的后面将对此进行更多讨论。

在这篇文章中,我分享了一段我为自己编写的代码。我当时正在确定 开始日期 and the 结束日期 of the 日期 dimension many times, so I thought it might help you as well.

这个怎么运作?

我在这篇文章中分享的Power Query表达式首先使用以下方法获取所有现有查询:

  • #sections intrinsic variable
  • 筛选出当前查询名称,即 GetMinMaxAllDates 在我的示例中,为避免出现以下错误:

Expression.Error: A 循环参考 was encountered during evaluation.

Expression.Error: A 循环参考 was encountered during evaluation.
  • 过滤掉查询  as type 表
  • 添加一个新的结构化列,名为 表格模式 包括表格的结构
  • 扩大 表格模式 结构化列保持 名称 and  列并重命名 名称 column to 柱 名称 and the  column to 数据类型
  • 过滤结果以仅保留其中任一列 日期 or 约会时间 datatypes
  • 从中过滤掉不必要的值 柱 名称 like 生日
  • 添加一个名为的新列 最小日期 获取出现在列中的最小值 柱 名称 出现在  column

嗯!我想太多了 提到  and  在不同的情况下。我希望我不会让它更加混乱.

  • 添加另一个名为的新列 最长日期 类似于我们创建 最小日期
  • 提取最小的 最小日期 column
  • 提取最大值 最长日期 column
  • 显示后两个列表

因此,如果您正在寻找解决方案,这里是 功率查询 我使用的表达式:

让
    AllQueries = #sections,
    RecordToTable = Record.ToTable(AllQueries[Section1]),
    FilterOutCurrentQuery = Table.SelectRows(RecordToTable, each [Name] <> "GetMinMaxAllDates"和Type.Is(Value.Type([Value]), type 表) = true),
    AddTableSchemaColumn = Table.AddColumn(FilterOutCurrentQuery, "表格模式", each try Table.Schema([Value]) otherwise null),
    ExpandTableSchema = Table.Buffer(Table.ExpandTableColumn(AddTableSchemaColumn, "表格模式",{"名称", "类"},{"柱 名称", "数据类型"})),
    FilterTypes = Table.SelectRows(ExpandTableSchema, each ([Datatype] = "约会时间" 要么 [Datatype] = "日期")),
    AddedMinDateColumn = Table.AddColumn(FilterTypes, "最小日期", each 日期.From(List.Min(Table.Column([Value], [Column 名称])))),
    AddedMaxDateColumn = Table.AddColumn(AddedMinDateColumn, "最长日期", each 日期.From(List.Max(Table.Column([Value], [Column 名称])))),
    FilterOutUnnecessaryColumns = Table.SelectRows(AddedMaxDateColumn, each ([Column 名称] <> "BirthDate")),
    最低日期 = List.Min(List.Combine({FilterOutUnnecessaryColumns[Min 日期], FilterOutUnnecessaryColumns[Max 日期]})),
    最大日期 = List.Max(List.Combine({FilterOutUnnecessaryColumns[Min 日期], FilterOutUnnecessaryColumns[Max 日期]})),
    MinMaxDates = {"最小日期 = " & Text.From(MinDate), "最长日期 = " & Text.From(MaxDate)}
in
        MinMaxDates

您可以下载以上表达式 从这里.

下图显示了在具有以下功能的Power Query Editor中运行上述代码的结果: 11 fact 表s 和 2 尺寸表。这些表有 17 columns with either 日期 or 约会时间 datatypes:

功率查询中的GetMinMaxAllDates查询

注意: 再一次,您需要在上面的表达式中传递当前的查询名称。就我而言,当前查询名称为 GetMinMaxAllDates 如下图所示:

Filtering out the current Query 名称

在本文的前面,我提到在很多情况下  want all 日期 or 约会时间 要涵盖的列 日期 表。一个很好的例子是 生日 and 逝世日期. If we do not note that then we can create a lot of irrelevant 日期s 在 our 日期 像我们得到的那样的表 最小日期 在上面的图像中 1916年2月10日. As you can 在 the image above there is a FilterOutUnnecessaryColumns step. We click on that step to filter the unnecessary 值s from the 柱 名称 列,如下图所示:

过滤出出生日期

单击最后一步是 MinMaxDates 以查看新值,如下图所示:

指出“出生日期”列后的“新最小日期”

By running the above query you get the valid 日期 range, so you can now create a 日期 表 with any method of choice, either 在 功率查询 要么 达克斯 using the above 日期 range. Remember, creating the 日期 表是完全独立的过程。该查询仅帮助我们在加载到Power Query Editor中的所有表中查找最小和最大有效日期。

注意事项

  • 上表总共有4000万行, GetMinMaxAllDates 查询在我的机器上运行了大约10秒钟,这还算不错。但是,在较大的表中,可能需要花费更多时间才能得出结果
  • 您必须已经将某些查询加载到Power BI编辑器中
  • 此方法也可以在直接查询模式下使用,但是您希望查询花费更多时间才能获得结果
  • The above query retrieves the min 日期 和 max 日期 across all 表s. When you create a 日期 表,请注意 日期 列应从 1月1日 of the min 日期 going all the way up to the 12月31日 of the max 日期
  • 此方法在Power BI Desktop RS中有效
  • 这个方法是 Power BI数据流支持

享受你的 约会!

6 thoughts on “在Power BI和Excel中的Power Query中查找所有表中的最小日期和最大日期

    1. 嗨Nate,

      欢迎使用BIInsight.com,并感谢您的光临。
      我想我必须更彻底地解释它,因此感谢您的宝贵反馈并将其引起我的注意。
      您必须将当前查询名称放入表达式中,以将其过滤掉,如下图所示,否则’ll get the “Cyclic…”错误,这是不愉快的!

      I’会在一分钟内更新帖子。

      干杯。

      1. 对…我问了这个之后,我想通了。虽然欣赏…肯定有道理。我现在可以开始工作了…但是现在更多的问题是如何使用PQ中的结果有效地建立日期表?我已经有一个PQ日期表,其中有一个“StartDate” 和 “EndDate”可以为每个报告设置的变量。我想用这些结果替换那些,并想知道我将使用什么代码来引用结果。

        1. 我知道!
          好吧,起初我的目标是使用上述过程的结果创建一个Date表,这是绝对合理的。
          不幸的是,Power BI没有’就像动态Power Query表达式一样,它会引发一条错误消息,抱怨如下,这是无稽之谈。

          返回错误:‘OLE DB或ODBC错误:[Expression.Error]我们无法将值null转换为逻辑类型。‘.

          Therefore, I decided to share only the Min/Max 日期 detection part.
          话虽这么说,使用上述方法动态创建日期表在Excel PQ中效果很好。
          稍后,我可能会写另一篇有关它的博客文章,但是一定要在PQ表达式中使用上面表达式的输出来生成Date表。
          如果您可以使用,请与我们分享您的经验。

          干杯。

          1. 我一直都在使用动态表达。这是我获得闵的解决方案& Max 日期 then generate a list of Calendar 日期.

            我使用了多种技术来自动生成日期。最初,您的内容然后来自堆栈溢出文章;“在Power BI / Power查询中添加缺少的日期行,并获取上面行的值”.

            查询注释:


            //获取所有查询的列表
            AllQueries = #sections,
            //将列表转换为表格
            RecordToTable = Record.ToTable(AllQueries [Section1]),
            //过滤表列表–筛选要使用的表
            #”FilterTables – Update List!”= Table.SelectRows(RecordToTable,每个([[Name] =“CallDetails” 要么 [Name] = “InteractionDetails”)),
            //获取表架构
            #”Added 表格模式” = Table.AddColumn(#”FilterTables – Update List!”, “TableSchema”,每个尝试Table.Schema([Value])否则为null),
            //展开架构
            #”Expanded 表格模式”= Table.ExpandTableColumn(#”Added 表格模式”, “TableSchema”, {“Name”, “Kind”}, {“Column”, “Datatype”}),
            //删除所有非日期列
            #”Filtered to 日期 & 约会时间”= Table.SelectRows(#”Expanded 表格模式”, each [Datatype] = “datetime” 要么 [Datatype] = “date”),
            //获取所有选定列的最短日期
            #”Add 最低日期” = Table.AddColumn(#”Filtered to 日期 & 约会时间”, “MinDate”,每个Date.From(List.Min(Table.Column([Value],[Column]))))),
            // Get Maximum 日期 for all selected 柱s
            #”Add 最大日期” = Table.AddColumn(#”Add 最低日期”, “MaxDate”,每个Date.From(List.Max(Table.Column([Value],[Column]))))),
            // convert to 日期 to prevent errors
            Convert2Date = Table.TransformColumnTypes(#”Add 最大日期”,{{“MinDate”, type 日期}, {“MaxDate”, type 日期}}),
            // set start 日期 variable by getting MIN 日期 from 最低日期 柱
            开始= List.Min(Convert2Date [MinDate]),
            // set end 日期 variable by getting MAX 日期 from 最大日期 柱
            结束= List.Max(Convert2Date [MaxDate]),
            // number of 日期s
            大小= Number.From(结束– start ) + 1,
            // Create 日期s from 最低日期 to 最大日期
            GenerateDates = List.Dates(开始,大小,#duration(1,0,0,0)),
            // new 表 with list of 日期s
            日期Table = Table.FromColumns({GenerateDates},{“CalendarDate”}),
            // join 日历日期 to existing 表
            已加入= Table.Join(DateTable,”CalendarDate”,Convert2Date,”MinDate”,JoinKind.FullOuter),
            //删除除“日历日期”以外的所有列
            #”RemCols<>CalendarDate”= Table.SelectColumns(Joined,{“CalendarDate”})
            //删除未使用的列

            #”RemCols<>CalendarDate”

          2. 嗨,丹,

            欢迎来到BIInsight.com。
            感谢您分享您的代码,对此我表示感谢。
            I’恐怕这种方法赢了’使用#sections内部变量时,在Power BI Desktop中工作。
            Power BI中不允许这样做。
            您可以在Excel中使用它,而在本地计算机上没有任何问题,但是将Excel文件发布到Power BI Service后,它就会中断。

            干杯。

有什么想法吗?在这里与我们分享:

该网站使用Akismet减少垃圾邮件。 了解如何处理您的评论数据.