OData Feed Analyser Customized Perform in Energy Question

Date:

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 information supply in Energy BI. One problem that I nearly at all times wouldn’t have a very good understanding of the underlying information mannequin. It may be actually laborious and time consuming if there isn’t a one within the enterprise that understands the underlying information mannequin. I do know, we are able to use $metadata to get the metadata schema from the OData feed, however let’s not go there. I’m not an OData knowledgeable however right here is the factor for somebody like me, I work with varied information sources which I’m not essentially an knowledgeable in, however I want to grasp what the entities are, how they’re related and many others… then what if I wouldn’t have entry any SMEs (Subject Matter Expert) who might 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 rely, their row rely (extra on this later), quantity and listing of associated tables, quantity and listing 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", "Identify"}, {"Column2", "Information"}}
      ),
    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 Depend", 
        every Desk.ColumnCount([Data]), 
        Int64.Sort
      ),
    TableCountRowsAdded = Desk.AddColumn(
        TableColumnCountAdded, 
        "Desk Row Depend", 
        every Desk.RowCount([Data]), 
        Int64.Sort
      ),
    NumberOfRelatedTablesAdded = Desk.AddColumn(
        TableCountRowsAdded, 
        "Variety of Associated Tables", 
        every Record.Depend(Desk.ColumnsOfType([Data], {Desk.Sort}))
      ),
    ListOfRelatedTables = Desk.AddColumn(
        NumberOfRelatedTablesAdded, 
        "Record of Associated Tables", 
        every 
          if [Number of Related Tables] = 0 then 
            null
          else 
            Desk.ColumnsOfType([Data], {Desk.Sort}), 
        Record.Sort
      ),
    NumberOfTextColumnsAdded = Desk.AddColumn(
        ListOfRelatedTables, 
        "Variety of Textual content Columns", 
        every Record.Depend(Desk.SelectRows([Schema], every Textual content.Incorporates([Kind], "textual content"))[Name]), 
        Int64.Sort
      ),
    ListOfTextColunmsAdded = Desk.AddColumn(
        NumberOfTextColumnsAdded, 
        "Record of Textual content Columns", 
        every 
          if [Number of Text Columns] = 0 then 
            null
          else 
            Desk.SelectRows([Schema], every Textual content.Incorporates([Kind], "textual content"))[Name]
      ),
    NumberOfNumericColumnsAdded = Desk.AddColumn(
        ListOfTextColunmsAdded, 
        "Variety of Numeric Columns", 
        every Record.Depend(Desk.SelectRows([Schema], every Textual content.Incorporates([Kind], "quantity"))[Name]), 
        Int64.Sort
      ),
    ListOfNumericColunmsAdded = Desk.AddColumn(
        NumberOfNumericColumnsAdded, 
        "Record of Numeric Columns", 
        every 
          if [Number of Numeric Columns] = 0 then 
            null
          else 
            Desk.SelectRows([Schema], every Textual content.Incorporates([Kind], "quantity"))[Name]
      ),
    NumberOfDecimalColumnsAdded = Desk.AddColumn(
        ListOfNumericColunmsAdded, 
        "Variety of Decimal Columns", 
        every Record.Depend(
            Desk.SelectRows([Schema], every Textual content.Incorporates([TypeName], "Decimal.Sort"))[Name]
          ), 
        Int64.Sort
      ),
    ListOfDcimalColunmsAdded = Desk.AddColumn(
        NumberOfDecimalColumnsAdded, 
        "Record of Decimal Columns", 
        every 
          if [Number of Decimal Columns] = 0 then 
            null
          else 
            Desk.SelectRows([Schema], every Textual content.Incorporates([TypeName], "Decimal.Sort"))[Name]
      ),
    #"Eliminated Different Columns" = Desk.SelectColumns(
        ListOfDcimalColunmsAdded, 
        {
          "Identify", 
          "Desk Column Depend", 
          "Desk Row Depend", 
          "Variety of Associated Tables", 
          "Record of Associated Tables", 
          "Variety of Textual content Columns", 
          "Record of Textual content Columns", 
          "Variety of Numeric Columns", 
          "Record of Numeric Columns", 
          "Variety of Decimal Columns", 
          "Record 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 effective in all the talked about information sources, for some information sources like Enterprise Central it isn’t fairly useful. So be conscious 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 a very good software.

As talked about earlier, the above perform exhibits tables’ column rely in addition to their row rely. On the latter, the row rely, I want to increase some extent. If the underlying desk has a variety of columns then the row rely 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 acquainted with the construction? Do have a greater thought? Please share your ideas within the feedback part under.

Oh! and… by the way in which, be at liberty to alter the above code and make it higher. Simply don’t forget to share the improved model with the group.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Share post:

Subscribe

spot_imgspot_img

Popular

More like this
Related

From Charges to Financial savings: Pursue Your Dream House

Step 3 – Plug in The Numbers: You'll...

IRS Warns Automobile Sellers About Phishing and Smishing Scams

The IRS is warning automobile sellers and sellers...

Microsoft Material Connections Demystified – BI Perception

Managing information connections in Microsoft Material might be...

Crafting a Complete Company Sustainability Coverage: A Step-by-Step Information

Conduct a complete evaluation of your...