Quick Tips: OData Feed Analyser Custom Function in Power Query

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

It’s been a while that I am working with OData 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 OData feed, but let’s not go there. I am not an OData expert but here is the thing for someone like me, I work with various data sources which I am not necessarily an expert in, but I need to understand what the entities are, how they are connected etc… then what if I do not have access any SMEs (Subject Matter Expert) who can help me with that?

So getting involved with more OData options, let’s get into it.

The custom function below accepts an OData 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"
Continue reading “Quick Tips: OData Feed Analyser Custom Function in Power Query”

Finding Minimum Date and Maximum Date Across All Tables in Power Query in Power BI and Excel

Finding Minimum Date and Maximum Date Across All Tables in Power Query in Power BI and Excel

When we talk about data analysis in Power BI, creating a Date table is inevitable. There are different methods to create a Date table either in DAX or in Power Query. In DAX you my use either CALENDAR() function or CALENDARAUTO() function to create the Date table. In Power Query you may use a combination of List.Dates()#date() and #duration() functions. Either way, there is one point that is always challenging and it is how to find out a proper date range, starting from a date in the past and ending with a date in the future, that covers all relevant dates within the data model. One simple answer is, we can ask the business. The SMEs know what the valid date range is..

While this is a correct argument it is not always the case. Especially with the Start Date which is a date in the past. In many cases the business says:

Lets’s have a look at the data to find out.

That is also a correct point, we can always a look at the data, find all columns with either Date or DateTime datatypes then sort the data in ascending or descending order to get the results. But what if there many of them? Then this process can be very time consuming.

Many of you may already thought that we can use CALENDARAUTO() in DAX and we are good to go. Well, that’s not quite right. In many cases there are some Date or DateTime columns that must not be considered in our Date dimension. Like Birth Date or Deceased Date. More on this later in this post.

In this post I share a piece of code I wrote for myself. I was in a situation to identify the Start Date and the End Date of the date dimension many times, so I thought it might help you as well.

How it works?

The Power Query expressions I share in this post starts with getting all existing queries using:

  • #sections intrinsic variable
  • Filtering out the current query name, which is GetMinMaxAllDates in my sample, to avoid getting the following error:

Expression.Error: A cyclic reference was encountered during evaluation.

Expression.Error: A cyclic reference was encountered during evaluation.
Continue reading “Finding Minimum Date and Maximum Date Across All Tables in Power Query in Power BI and Excel”

Quick Tips: Converting Hexadecimal, Oct and Binary to Decimal in a Single Power Query Function

A Power Query Function to Convert HEX, OCT and BIN values to DEC

A while ago I wrote a blogpost on how to use Unicode characters in Power BI. In that blogpost I used a recursive Power Query function to convert Hex values to Dec values. A few weeks back one of my site visitors kindly shared his non-recursive version of Power Query function which beautifully does the job. A big shout out to Rocco Lupoi for sharing his code. So, I decided to share it with everyone so more people can leverage his nice Power Query function. I have touched his code a bit though, but it was more of a cosmetic change, so all credits of this blogpost goes to Rocco. The benefits of his code is not limited to being non-recursive. The code below converts numbers of any base when the base is smaller than 16 like Binary and Oct, so it is not limited to Hex values only. The other benefit of the below code is that it is not case sensitive (note to the digits step on the code below).

Here is the fnHex2Dec function for Power Query:

(input as text, optional base as number) as number =>
let
        values = [
                0=0,
                1=1,
                2=2,
                3=3,
                4=4,
                5=5,
                6=6,
                7=7,
                8=8,
                9=9,
                A=10,
                B=11,
                C=12,
                D=13,
                E=14,
                F=15
        ],
        digits = Text.ToList(Text.Upper(input)),
        dim = List.Count(digits)-1,
        exp = if base=null then 16 else base,
        Result = List.Sum(
                        List.Transform(
                                {0..dim}
                                , each Record.Field(values, digits{_}) * Number.Power(exp, dim - _)
                                )
                        )
in
        Result

As you see in the code above, the base parameter is optional, so if not provided base 16 would be the default.

This is how we can invoke the above function:

fnHex2Dec("AbCdEf", null)
Invoking fnHex2Dec function to convert numbers of any base to decimal
Continue reading “Quick Tips: Converting Hexadecimal, Oct and Binary to Decimal in a Single Power Query Function”

What Does XMLA Endpoints Mean for Power BI and How to Test it for Free?

Test Environment from Power BI XMLA Endpoint

XMLA endpoint connectivity for public preview has been announced late March 2019. As at today, it is only available for Power BI Premium capacity users. This sounds like a massive restriction to a lot of people who don’t have a Premium capacity, but they’d love to see how it works. In this article I show you an easy way to get your hands to Power BI XMLA endpoint as quick as possible. Before I start, I’d like to simply explain what XMLA endpoint is and what it really means for Power BI users.

Power BI is Like Onion! It has layers!

Generally speaking, Power BI has two different layers, presentation layer and data model layer. Presentation layer is the visual layer, the one you make all those compelling reports and visualisations. The data model as the name resembles, is the layer that you make your data model in. This layer is the one you can access it via XMLA connectivity.

In a Power BI Desktop file, you can see both layers:

Different layers of Power BI

How XMLA Relates to Different Layers in Power BI?

As you may have already guessed, XMLA is only related to the data model layer and it has nothing to do with the presentation layer. So you may connect to a data model, browse the data model, import data from the model to other platforms like Excel and so forth.

XMLA Is Not New!

Seriously? Yes, seriously. It is not new. It’s been around for many years and perhaps you’ve already used it zillions of times. Whenever you’re connecting to an instance of SQL Server Analysis Services, either Multidimensional or Tabular from any tools like SQL Server Management Studio (SSMS), Power BI Report Builder, Excel, Tableau, etc…, you’re using XMLA connectivity indeed.

Power BI is an Instance of SSAS Tabular

It is true. Power BI runs a local instance of SSAS Tabular model. So, whenever you open a Power BI Desktop file (PBIX), Power BI creates a local instance of SSAS Tabular model with a random local port number that can be accessed on your local machine only. When you close the file, the local instance of SSAS Tabular is shut down and its port number is released.

I first revealed the fact that you can connect to the underlying data model in Power BI Desktop from whole different range of tools like SSMS, SQL Server Profiler, Excel, etc… on Jun 2016. So, we indeed were using XMLA to connect to Power BI data models for a long time. We can even take a step further to import our Power BI data models into an instance of SSAS Tabular. In that sense, we are literally generating XMLA scripts from Power BI to create the same data model in SSAS Tabular. How cool is that?

Sooo… What is new then?

Continue reading “What Does XMLA Endpoints Mean for Power BI and How to Test it for Free?”