快速提示:Power Query中的OData Feed Analyzer定制功能

Power BI和Excel的Power Query中的OData Feed Analyzer自定义功能

It’s been a while that I am working with 数据 data source 在 Power BI. One challenge that I almost always do not have a good understanding of the underlying data model. It can be really hard 和 time consuming if there is no one 在 the business that understands the underlying data model. I know, we can use $metadata to get the metadata schema from the 数据 feed, but let’不去那里。我不是OData专家,但是这里是像我这样的人的事,我使用的数据源不一定是专家,但我需要了解实体是什么,它们如何连接等…那如果我没有任何中小型企业(S对象 M东北黑钙土 Expert)谁可以帮助我?

因此,参与更多OData选项,让’s get 在to it.

The custom function below accepts an 数据 URL then it discovers all tables, their column count, their row count (more on this later), number 和 list of related tables, number 和 list of columns of type text, type numberDecimal.Type.

// fnODataFeedAnalyser
(ODataFeed as text) => 
  let
    Source = OData.Feed(ODataFeed),
    SourceToTable = Table.RenameColumns(
        Table.DemoteHeaders(Table.FromValue(Source)), 
        {{"Column1", "Name"}, {"Column2", "Data"}}
      ),
    FilterTables = Table.SelectRows(
        SourceToTable, 
        each Type.Is(Value.Type([Data]), Table.Type) = true
      ),
    SchemaAdded = Table.AddColumn(FilterTables, "Schema", each Table.Schema([Data])),
    TableColumnCountAdded = Table.AddColumn(
        SchemaAdded, 
        "Table Column Count", 
        each Table.ColumnCount([Data]), 
        Int64.Type
      ),
    TableCountRowsAdded = Table.AddColumn(
        TableColumnCountAdded, 
        "Table Row Count", 
        each Table.RowCount([Data]), 
        Int64.Type
      ),
    NumberOfRelatedTablesAdded = Table.AddColumn(
        TableCountRowsAdded, 
        "Number of Related Tables", 
        each List.Count(Table.ColumnsOfType([Data], {Table.Type}))
      ),
    ListOfRelatedTables = Table.AddColumn(
        NumberOfRelatedTablesAdded, 
        "List of Related Tables", 
        each 
          if [Number of Related Tables] = 0 then 
            null
          else 
            Table.ColumnsOfType([Data], {Table.Type}), 
        List.Type
      ),
    NumberOfTextColumnsAdded = Table.AddColumn(
        ListOfRelatedTables, 
        "Number of Text Columns", 
        each List.Count(Table.SelectRows([Schema], each Text.Contains([Kind], "text"))[Name]), 
        Int64.Type
      ),
    ListOfTextColunmsAdded = Table.AddColumn(
        NumberOfTextColumnsAdded, 
        "List of Text Columns", 
        each 
          if [Number of Text Columns] = 0 then 
            null
          else 
            Table.SelectRows([Schema], each Text.Contains([Kind], "text"))[Name]
      ),
    NumberOfNumericColumnsAdded = Table.AddColumn(
        ListOfTextColunmsAdded, 
        "Number of Numeric Columns", 
        each List.Count(Table.SelectRows([Schema], each Text.Contains([Kind], "number"))[Name]), 
        Int64.Type
      ),
    ListOfNumericColunmsAdded = Table.AddColumn(
        NumberOfNumericColumnsAdded, 
        "List of Numeric Columns", 
        each 
          if [Number of Numeric Columns] = 0 then 
            null
          else 
            Table.SelectRows([Schema], each Text.Contains([Kind], "number"))[Name]
      ),
    NumberOfDecimalColumnsAdded = Table.AddColumn(
        ListOfNumericColunmsAdded, 
        "Number of Decimal Columns", 
        each List.Count(
            Table.SelectRows([Schema], each Text.Contains([TypeName], "Decimal.Type"))[Name]
          ), 
        Int64.Type
      ),
    ListOfDcimalColunmsAdded = Table.AddColumn(
        NumberOfDecimalColumnsAdded, 
        "List of Decimal Columns", 
        each 
          if [Number of Decimal Columns] = 0 then 
            null
          else 
            Table.SelectRows([Schema], each Text.Contains([TypeName], "Decimal.Type"))[Name]
      ),
    #"Removed Other Columns" = Table.SelectColumns(
        ListOfDcimalColunmsAdded, 
        {
          "Name", 
          "Table Column Count", 
          "Table Row Count", 
          "Number of Related Tables", 
          "List of Related Tables", 
          "Number of Text Columns", 
          "List of Text Columns", 
          "Number of Numeric Columns", 
          "List of Numeric Columns", 
          "Number of Decimal Columns", 
          "List of Decimal Columns"
        }
      )
  in
    #"Removed Other Columns"
继续阅读 “快速提示:Power Query中的OData Feed Analyzer定制功能”

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

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

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

尽管这是一个正确的论点,但并非总是如此。特别是 开始日期 这是过去的日期。在许多情况下,企业会说:

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

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

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

在这篇文章中,我分享了一段我为自己编写的代码。我当时正在确定 开始日期 and the 结束日期 日期维度的次数很多,所以我认为这可能对您也有帮助。

这个怎么运作?

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

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

Expression.Error:评估期间遇到循环引用。

Expression.Error:评估期间遇到循环引用。
继续阅读 “在Power BI和Excel中的Power Query中查找所有表中的最小日期和最大日期”

快速技巧,Power BI Desktop,查询参数,第4部分,将Power Query参数值传递到SQL 服务器存储过程

我过去写过3篇关于查询参数的博文。

这是快速提示形式的第四篇。这是场景。我的一位客户要求从SQL 服务器的存储过程中获取数据。她需要将值从“查询参数”传递回SQL 服务器,并在Power BI中获得结果。

解决方案有点简单。我在中创建了一个简单的存储过程 AdventureWorksDW2019 如下:

CREATE PROCEDURE SP_Sales_by_Date 
	@date 在t
AS
BEGIN
	SET NOCOUNT ON;
	SELECT *
	FROM [dbo].[FactInternetSales]
	WHERE OrderDateKey >= @date
END
GO

在Power BI Desktop中,从SQL 服务器获取数据,然后:

  • 输入 服务器 名称
  • 输入 数据库 名称
  • 选择 数据连接模式
  • 展开 高级选项
  • 输入一个 的SQL语句 调用存储过程,如下所示:
exec SP_Sales_by_Date @date = 20140101
  • 点击确定
使用Power BI Desktop中的SQL语句从SQL 服务器获取数据
  • 请点击 转换资料
在Power BI Desktop中转换数据

现在我们需要创建一个查询参数。在我的示例中,我创建了一个 日期键 小数 数据类型:

在Power BI Desktop中创建新的查询参数
继续阅读 “快速技巧,Power BI Desktop,查询参数,第4部分,将Power Query参数值传递到SQL 服务器存储过程”

快速提示: 时间 Dimension with 时间 Bands at Seconds Granularity 在 Power BI 和 SSAS表格

时间 Dimension with 时间 Bands at Seconds Granularity 在 Power BI 和 SSAS表格

过去我也写过其他有关此主题的文章,您可以找到它们 这里 这里。在第一篇文章中,我解释了如何创建“Time”时间带的维度 分钟 粒度。然后,我的一位客户要求“Time”几秒钟内的尺寸,这鼓励了我写第二篇博客文章。在第二篇博文中,虽然我没有’t做时间带,所以我在这里写第三篇文章,这是第二篇文章支持5分钟,15分钟,30分钟,45分钟和60分钟的时间带的变体。“Time”尺寸降至秒。在这篇快速文章中,我直接跳到重点,向您展示如何生成“Time”维度以三种不同的方式,即在SQL 服务器中使用T-SQL,使用Power Query(M)和DAX。然后是这里:

时间 Dimension at Second Grain with 功率查询 (M) Supporting 时间 Bands:

在查询编辑器中复制/粘贴以下代码’s Advanced Editor to generate 时间 dimension 在 功率查询:

let
Source = Table.FromList({1..86400}, Splitter.SplitByNothing()),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Column1", "ID"}}),
    #"时间 Column Added" = Table.AddColumn(#"Renamed Columns", "时间", each 时间.From(#datetime(1970,1,1,0,0,0) + #duration(0,0,0,[ID]))),
    #"Hour Added" = Table.AddColumn(#"时间 Column Added", "Hour", each 时间.Hour([Time])),
    #"Minute Added" = Table.AddColumn(#"Hour Added", "Minute", each 时间.Minute([Time])),
    #"5 Min Band Added" = Table.AddColumn(#"Minute Added", "5 Min Band", each 时间.From(#datetime(1970,1,1,0,0,0) + #duration(0, 0, Number.RoundDown(Time.Minute([Time])/5) * 5, 0))  +  #duration(0, 0, 5, 0)),
    #"15 Min Band Added" = Table.AddColumn(#"5 Min Band Added", "15 Min Band", each 时间.From(#datetime(1970,1,1,0,0,0) + #duration(0, 0, Number.RoundDown(Time.Minute([Time])/15) * 15, 0))  +  #duration(0, 0, 15, 0)),
    #"30 Min Band Added" = Table.AddColumn(#"15 Min Band Added", "30 Min Band", each 时间.From(#datetime(1970,1,1,0,0,0) + #duration(0, 0, Number.RoundDown(Time.Minute([Time])/30) * 30, 0))  +  #duration(0, 0, 30, 0)),
    #"45 Min Band Added" = Table.AddColumn(#"30 Min Band Added", "45 Min Band", each 时间.From(#datetime(1970,1,1,0,0,0) + #duration(0, 0, Number.RoundDown(Time.Minute([Time])/45) * 45, 0))  +  #duration(0, 0, 45, 0)),
    #"60 Min Band Added" = Table.AddColumn(#"45 Min Band Added", "60 Min Band", each 时间.From(#datetime(1970,1,1,0,0,0) + #duration(0, 0, Number.RoundDown(Time.Minute([Time])/60) * 60, 0))  +  #duration(0, 0, 60, 0)),
    #"Removed Other Columns" = Table.SelectColumns(#"60 Min Band Added",{"时间", "Hour", "Minute", "5 Min Band", "15 Min Band", "30 Min Band", "45 Min Band", "60 Min Band"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"时间", type time}, {"Hour", Int64.Type}, {"Minute", Int64.Type}, {"5 Min Band", type time}, {"15 Min Band", type time}, {"30 Min Band", type time}, {"45 Min Band", type time}, {"60 Min Band", type time}})
in
#"Changed Type"
继续阅读 “Quick Tips: 时间 Dimension with 时间 Bands at Seconds Granularity 在 Power BI 和 SSAS表格”