快速提示: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 in Power BI. One challenge that I almost always do not have a good understanding of the underlying data model. It can be really hard and time consuming if there is no one in 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 into it.

The custom function below accepts an 数据 URL then it discovers all tables, their column count, their row count (more on this later), number and list of related tables, number and list of columns of type text, type number and Decimal.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 Service中启用数据流

Power BI服务中的数据流

Power BI服务中的数据流(预览)已于昨天(2018年11月6日)登陆。我在启用此炫酷的新功能时遇到了一些困难,因此我认为写一个快速提示是个好方法。尽管在撰写此快速提示之时尚不了解Dataflows,但未来的情况可能会完全不同。

可以立即以Power BI Premium容量或Power BI Embedded容量使用功能齐全的数据流,但是尽管仍处于预览状态,但您可以利用Power BI Pro许可证中的有限功能。功能如“来自其他数据流的链接实体” or “Computed Entities”在Power BI Pro许可证中不可用,例如将表合并到新表。

数据流 计算实体

启用数据流

  • 登录Power BI服务后,单击“Settings”
  • 请点击“Admin Portal”

Power BI服务管理门户

  • 选择您所在的容量类型,高级或嵌入式
  • 单击您想要的容量’d想启用数据流

在Power BI管理员门户中管理高级容量

  • 向下滚动以查找并单击“Workloads” under “More Options”
  • 启用“Dataflows (Preview)”
  • 如果您坚持默认“Max Memory (%)”值设置为20’会收到一条错误消息说“更新您的工作负载设置时出现问题。请稍后再试”。该错误消息根本没有帮助。您收到错误消息的原因是“Max Memory (%)”值必须是27到100之间的数字,默认值为20。

在Power BI Service中启用数据流 继续阅读 “快速提示:如何在Power BI Service中启用数据流”