Understanding Row Context in DAX and Power BI

Hey all! In my latest YouTube video I discuss Row Context in Data Analysis Expressions (DAX). Row context by itself is a very easy concept but I’ve seen, quite consistently, that many people are confused by row context and most of this confusion is a result of other concepts like nested row context and evaluation context. I will be tackling those topics in future posts, but for now, let’s take a peek at row context.

What is Row Context in DAX?

Row context is created by calculated columns and iterator functions in DAX. This is because these operations work on one row at a time when performing expressions. A common side effect of row context is that the active filter in your data model is disabled by default. I discuss this behavior in the video below as well as how to work around it using navigation functions.

What are Navigation functions in DAX? (Related and RelatedTable)

RELATED and RELATEDTABLE are functions that allow the developer to use the existing active relationship when a row context is present and the filter has been disabled. Which one you use depends on which side of the relationship you are on. For example, use RELATED when you are on the many side of the relationship and RELATEDTABLE when on the one side of the relationship.

Thanks for watching!

Azure Data Factory–Rule Based Mapping and This($$) Function

Hello! This is the eight video in a series of videos that will be posted on Azure Data Factory! Feel free to follow this series and other videos I post on YouTube! Remember to  like, subscribe and encourage me to keep posting new videos! Smile

Schema flexibility and late schema binding really separates Azure Data Factory from its’ on-prem rival SQL Server Integration Services (SSIS). This video focuses on leveraging the capability of flexible schemas and how rules can be defined to map changing column names to the sink.

Rule Based Mapping

Rule based mapping in ADF allows you to define rules where you can map columns that come into a data flow to a specific column. For example, you can map a column that has ‘date’ anywhere in the name to a column named ‘Order_Date’. This ability to define rules based allows for very flexible and reusable data flows, in the video below I walk through and explain how to set this up in side of a Select transform, enjoy!

This ( $$ ) Function in a Derived transform and a Select Transform

The this ($$) function simply returns the name of the column or value of the column depending on where it is used. In this video I show two use cases, one in a Select transform and one in a Derived transform.

Video Below:

If you like what you see and want more structured end to end training then check out the training offerings for Pragmatic Works! https://pragmaticworks.com/training

Azure Data Factory–Executing an ADF Pipeline from Azure Logic Apps

Hello! This is the seventh video in a series of videos that will be posted on Azure Data Factory! Feel free to follow this series and other videos I post on YouTube! Remember to  like, subscribe and encourage me to keep posting new videos! Smile

This video in the series highlights Azure Data Factory integration with Azure Logic Apps!

Azure Logic Apps

Azure Logic apps is a great way of extending the capability of different services in Azure. In this video I take a look at how we can use Azure Logic Apps to perform a wide array of event based triggers for a Data Factory Pipeline

Azure Logic Apps – Create a pipeline run (Executing a Data Factory Pipeline)

We are going to execute a Data Factory pipeline run using the action “Create a pipeline run” in Azure Logic Apps. The biggest challenge here is understanding and learning how to pass parameters into your data factory pipeline! I show this in the video but I will also provide the code snippet here for reference. This can be modified as necessary! Enjoy.

image

Video Below:

If you like what you see and want more structured end to end training then check out the training offerings for Pragmatic Works! https://pragmaticworks.com/training

Azure Data Factory–Web Activity / Azure Logic Apps

Hello! This is the fifth video in a series of videos that will be posted on Azure Data Factory! Feel free to follow this series and other videos I post on YouTube! Remember to  like, subscribe and encourage me to keep posting new videos! Smile

This video in the series highlights Azure Data Factory integration with Azure Logic Apps!

Web Activity in ADF v2

The web activity within Azure Data Factory allows you to call a custom REST endpoint from an ADF control flow. In this video we make a POST API Method call to Azure Logic Apps.

Azure Logic Apps

Azure Logic Apps is a great automated tool for building automated workflows and integrates really well with Azure Data Factory!

Video Below:

If you like what you see and want more structured end to end training then check out the training offerings for Pragmatic Works! https://pragmaticworks.com/training

Azure Data Factory–Copy and Delete Activities

Hello! This is the fifth video in a series of videos that will be posted on Azure Data Factory! Feel free to follow this series and other videos I post on YouTube! Remember to  like, subscribe and encourage me to keep posting new videos! Smile

This video in the series leverages the combination of copy and delete activities to archive files once they have been processed.

Copy Activity in ADF v2

The copy activity within Azure Data Factory allows you to efficiently move data from a source to a destination. The copy activity is highly customizable making reusability extremely easy through parameterization. In this video we will copy a file from one blob container to another.

Delete Activity in Azure Data Factory

The delete activity will allow you to delete files or folders either in an on-prem environment or in a cloud environment. In this demo we first move the file using the copy activity and then delete the file from the source with the delete activity!

Video Below:

 

If you like what you see and want more structured end to end training then check out the training offerings for Pragmatic Works! https://pragmaticworks.com/training

Azure Data Factory–Foreach / Filter activities

Hello! This is the fourth video in a series of videos that will be posted on Azure Data Factory! Feel free to follow this series and other videos I post on YouTube! Remember to  like, subscribe and encourage me to keep posting new videos! Smile

This video in the series leverages and explores the filter activity and foreach activity within Azure Data Factory. Please take a look at a quick overview below and then watch the video! Enjoy!

Filter Activity in Azure Data Factory

The filter activity in ADF is used in a control flow to filter down an existing array. The filter activity will take an input array and then output a separate array that has the filtered results. Each row of the input array is evaluated using the expression language within Azure Data Factory.

Foreach Activity in Azure Data Factory

The foreach activity iterates over an array performing an action or set of actions on each item within that list. In this video each item in the array is passed into a stored procedure activity, which then loads specific metadata information into a table in an Azure SQL Database.

Video on YouTube:

 

If you like what you see and want more structured end to end training then check out the training offerings for Pragmatic Works! https://pragmaticworks.com/training

Azure Data Factory–Lookup and If Condition activities (Part 3)

Hello! This is the third video in a series of videos that will be posted on Azure Data Factory! Feel free to follow this series and other videos I post on YouTube! Remember to  like, subscribe and encourage me to keep posting new videos! Smile

This video in the series leverages the lookup and if condition activity to return a set of results and then determine what operation should occur next based on an expression within the control flow. This is a great demo for learning new activities, expressions and referencing output parameters. Below is a couple of quick highlights on each of the activities featured here and then a link to the ADF video!

Lookup Activity in ADF v2

The lookup activity within Azure Data Factory allows you to execute a stored procedure and return an output. Interestingly, the stored procedure activity does not return any outputs.

  • Leverage the Lookup activity to execute SQL Code or a Stored procedure and return an output.

If Condition Activity in Azure Data Factory

  • Leverage the If Condition activity and ADF Expression language to help control operations in the ADF Control Flow

Video Below:

If you like what you see and want more structured end to end training then check out the training offerings for Pragmatic Works! https://pragmaticworks.com/training

Azure Data Factory – Stored Procedure Activity (Part 2)

Hello! This is the second video in a series of videos that will be posted on Azure Data Factory! Feel free to follow this series and other videos I post on YouTube! Remember to  like, subscribe and encourage me to keep posting new videos! Smile

First blog in series: Azure Data Factory – Metadata Activity

Stored Procedure Activity in ADF v2

  • Writing data to an Azure SQL Database via a stored procedure.
  • Populating input parameters from the output properties of other activities in ADF.
  • Limitations for the Stored Procedure activity

Video Below:

If you like what you see and want more structured end to end training then check out the training offerings for Pragmatic Works! https://pragmaticworks.com/training

Using R in Power BI to check if file exist

I have done a series of blog posts on how R can be used in Power BI. I have also created quick videos for each of those posts and I will do the same for this post. I find it easier and more efficient to share this information via video than through a narrative with screenshots. In this blog post, I’m going to share the code that can be used to check if a file exist prior to processing of that file. I will record a video that goes in more depth and include that in the blog post later.

Check if file exist

As someone who comes from an enterprise BI background, I am always looking for ways to handle things I would have previously done with SSIS. With R integration we have a lot of new possibilities.

The Code:

fileexists <- FALSE
fileName   <- “C:\\Backup\\Blogs\\R – Check if File Exist\\CustomerSales1.csv

while (fileexists == FALSE)
    {
   #fileexists = TRUE ##Test Expression
   fileexists <-
            if(
                file.exists(fileName))
               {TRUE} else {FALSE}
   print(fileexists)
   ## Add 3 second Delay
   Sys.sleep(3)   
    }
   
    data  = read.csv(fileName)
    head(data)