Saturday, May 21, 2022
HomeBusiness IntelligenceFast Suggestions: OData Feed Analyser Customized Perform in Energy Question

Fast Suggestions: OData Feed Analyser Customized Perform in Energy Question


OData Feed Analyser Custom Function in Power Query for Power BI and Excel

It’s been some time that I’m working with OData knowledge supply in Energy BI. One problem that I virtually at all times should not have an excellent understanding of the underlying knowledge mannequin. It may be actually exhausting and time consuming if there is no such thing as a one within the enterprise that understands the underlying knowledge mannequin. I do know, we will use $metadata to get the metadata schema from the OData feed, however let’s not go there. I’m not an OData professional however right here is the factor for somebody like me, I work with varied knowledge sources which I’m not essentially an professional in, however I want to grasp what the entities are, how they’re linked and so on… then what if I should not have entry any SMEs (Subject Matter Expert) who will help me with that?

So getting concerned with extra OData choices, let’s get into it.

The customized perform under accepts an OData URL then it discovers all tables, their column depend, their row depend (extra on this later), quantity and record of associated tables, quantity and record of columns of kind textual content, kind quantity and Decimal.Sort.

// fnODataFeedAnalyser
(ODataFeed as textual content) => 
  let
    Supply = OData.Feed(ODataFeed),
    SourceToTable = Desk.RenameColumns(
        Desk.DemoteHeaders(Desk.FromValue(Supply)), 
        {{"Column1", "Title"}, {"Column2", "Knowledge"}}
      ),
    FilterTables = Desk.SelectRows(
        SourceToTable, 
        every Sort.Is(Worth.Sort([Data]), Desk.Sort) = true
      ),
    SchemaAdded = Desk.AddColumn(FilterTables, "Schema", every Desk.Schema([Data])),
    TableColumnCountAdded = Desk.AddColumn(
        SchemaAdded, 
        "Desk Column Rely", 
        every Desk.ColumnCount([Data]), 
        Int64.Sort
      ),
    TableCountRowsAdded = Desk.AddColumn(
        TableColumnCountAdded, 
        "Desk Row Rely", 
        every Desk.RowCount([Data]), 
        Int64.Sort
      ),
    NumberOfRelatedTablesAdded = Desk.AddColumn(
        TableCountRowsAdded, 
        "Variety of Associated Tables", 
        every Listing.Rely(Desk.ColumnsOfType([Data], {Desk.Sort}))
      ),
    ListOfRelatedTables = Desk.AddColumn(
        NumberOfRelatedTablesAdded, 
        "Listing of Associated Tables", 
        every 
          if [Number of Related Tables] = 0 then 
            null
          else 
            Desk.ColumnsOfType([Data], {Desk.Sort}), 
        Listing.Sort
      ),
    NumberOfTextColumnsAdded = Desk.AddColumn(
        ListOfRelatedTables, 
        "Variety of Textual content Columns", 
        every Listing.Rely(Desk.SelectRows([Schema], every Textual content.Accommodates([Kind], "textual content"))[Name]), 
        Int64.Sort
      ),
    ListOfTextColunmsAdded = Desk.AddColumn(
        NumberOfTextColumnsAdded, 
        "Listing of Textual content Columns", 
        every 
          if [Number of Text Columns] = 0 then 
            null
          else 
            Desk.SelectRows([Schema], every Textual content.Accommodates([Kind], "textual content"))[Name]
      ),
    NumberOfNumericColumnsAdded = Desk.AddColumn(
        ListOfTextColunmsAdded, 
        "Variety of Numeric Columns", 
        every Listing.Rely(Desk.SelectRows([Schema], every Textual content.Accommodates([Kind], "quantity"))[Name]), 
        Int64.Sort
      ),
    ListOfNumericColunmsAdded = Desk.AddColumn(
        NumberOfNumericColumnsAdded, 
        "Listing of Numeric Columns", 
        every 
          if [Number of Numeric Columns] = 0 then 
            null
          else 
            Desk.SelectRows([Schema], every Textual content.Accommodates([Kind], "quantity"))[Name]
      ),
    NumberOfDecimalColumnsAdded = Desk.AddColumn(
        ListOfNumericColunmsAdded, 
        "Variety of Decimal Columns", 
        every Listing.Rely(
            Desk.SelectRows([Schema], every Textual content.Accommodates([TypeName], "Decimal.Sort"))[Name]
          ), 
        Int64.Sort
      ),
    ListOfDcimalColunmsAdded = Desk.AddColumn(
        NumberOfDecimalColumnsAdded, 
        "Listing of Decimal Columns", 
        every 
          if [Number of Decimal Columns] = 0 then 
            null
          else 
            Desk.SelectRows([Schema], every Textual content.Accommodates([TypeName], "Decimal.Sort"))[Name]
      ),
    #"Eliminated Different Columns" = Desk.SelectColumns(
        ListOfDcimalColunmsAdded, 
        {
          "Title", 
          "Desk Column Rely", 
          "Desk Row Rely", 
          "Variety of Associated Tables", 
          "Listing of Associated Tables", 
          "Variety of Textual content Columns", 
          "Listing of Textual content Columns", 
          "Variety of Numeric Columns", 
          "Listing of Numeric Columns", 
          "Variety of Decimal Columns", 
          "Listing of Decimal Columns"
        }
      )
  in
    #"Eliminated Different Columns"

Right here is the GitHub hyperlink for the above code.

I used this perform for preliminary investigation on varied OData sources together with Microsoft Challenge On-line, Microsoft Enterprise Central, some third social gathering instruments and naturally Northwind pattern. Whereas it really works high quality in all the talked about knowledge sources, for some knowledge sources like Enterprise Central it’s not fairly useful. So be aware of that.

I used Energy Question formatter to format the above code. I simply polished it a bit to suit it to my style. Give it a go, it’s an excellent instrument.

As talked about earlier, the above perform exhibits tables’ column depend in addition to their row depend. On the latter, the row depend, I wish to increase some extent. If the underlying desk has a variety of columns then the row depend calculation could take a very long time.

The screenshot under exhibits the outcomes of the fnODataFeedAnalyser perform invoked for a Microsoft Challenge On-line and it took a wee bit lower than 3 minutes to run.

Outcomes of invoking the fnODataFeedAnalyser customized perform for Microsoft Challenge On-line

Have you ever used this technique earlier than to analyse a dataset that you’re not accustomed to the construction? Do have a greater thought? Please share your ideas within the feedback part under.

Oh! and… by the best way, be happy to alter the above code and make it higher. Simply don’t forget to share the improved model with the neighborhood.

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Recent Comments