Quick Tips: Renaming All Tables’ Columns in One Go in Power Query

Renaming All Tables' Columns in One Go in Power Query

I previously wrote a blog post explaining how to rename all columns in a table in one go with Power Query. One of my visitors raised a question in the comments about the possibility to rename all columns from all tables in one go. Interestingly enough, one of my customers had a similar requirement. So I thought it is good to write a Quick Tip explaining how to meet the requirement.

The Problem

You are connecting to the data sources from Power BI Desktop (or Excel or Data Flows). The columns of the source tables are not user friendly, so you require to rename all columns. You already know how to rename all columns of a table in one go but you’d like to apply the renaming columns patterns to all tables.

The Solution

The solution is quite simple. We require to connect to the source, but we do not navigate to any tables straight away. In my case, my source table is an on-premises SQL Server. So I connect to the SQL Server instance using the Sql.Database(Server, DB) function in Power Query where the Server and the DB are query parameters. Read more about query parameters here. The results would like the following image:

The Results of Sql.Database() Function in Power Query
The results of running the Sql.Database(Server, DB) function

As you see in the above image, the results include Tables, Views and Functions. We are not interested in Functions therefore we just filter them out. The following image shows the results after applying the filter:

Filtering out SQL Server Functions After Connecting from Power Query
Filtering out SQL Server Functions

If we look closer to the Data column, we see that the column is indeed a Structured Column. The structured values of the Data column are Table values. If we click on a cell (not on the Table value of the cell), we can see the actual underlying data, as shown in the following image:

Continue reading “Quick Tips: Renaming All Tables’ Columns in One Go in Power Query”

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”

Quick Tips: How to Enable Dataflows In Power BI Service

Dataflows in Power BI Service

Dataflows (Preview) in Power BI Service has been landed yesterday (6th November 2018). I had a little bit of difficulties to enable this cool new feature so I thought it is good to write a Quick tip about it. While Dataflows is under preveiw at the time of writing this quick tip, the situation may be totally different in the future.

Straight away, fully featured Dataflows is available in a Power BI Premium capacity or in a Power BI Embedded Capacity, but, while this is still in preview, you can take advantage of limited features available in your Power BI Pro license. Features like “Linked entities from other dataflows” or “Computed Entities”, like merging tables to a new table, are not available in a Power BI Pro license.

Dataflows Computed Entities

Enabling Dataflows

  • After sign in to Power BI Service click “Settings”
  • Click “Admin Portal”

Power BI Service Admin Portal

  • Select Capacity type you are in, either Premium or Embedded
  • Click on a desired capacity that you’d like to enable Dataflows

Managing a Premium Capacity in Power BI Admin Portal

  • Scroll down to find and click “Workloads” under “More Options”
  • Enable “Dataflows (Preview)”
  • If you stick to the default “Max Memory (%)” value that is set to 20 you’ll get an error message saying “There was an issue updating your workload setting. Try again in a little while”. The error message is not helpful at all. The reason you get the error message is that the “Max Memory (%)” value must be a number between 27 to 100 while the default is 20.

Enabling Dataflows in Power BI Service Continue reading “Quick Tips: How to Enable Dataflows In Power BI Service”