PowerBI monthly report refresh

How to setup monthly refresh for PowerBI ?

PowerBI generally allows daily or weekly refreshes with options of selecting multiple time values. But if we want to refresh it once in a month then we have two options.

• Using PowerBI API and ADF
• Using Logic Apps and ADF

For both options we need to have a powerbi user. For API we can use the app based authentication as well and then we can call the dataset refresh api. 

Option 1 API 

In this option we need to use ADF web activity where in first web activity we will need to get tokens which can be used to call the dataset refresh api method. Once we are good and pipeline wiorks well then we can go ahead and create a monthly schedule trigger.

Option 2 

Here we will need a valid power bi user and logic app. We go to the logic app authoring page and set heep request action and then next step will be powerbi dataset refresh. This will require a powerbi connection to be created from logic app. The method will need the dataset id which we can obtain by going to powerbi.com. Next step is same as previous i.e create an ADF pipeline with one web activity which will call the logic app using http request. Now create ADF pipeline monthly schedule trigger. 

Write a python program to find factorial of a number ?

Factorial 

When we recursively multiply number incrementing from 1 to a given number then its called factorial of that number. We use the notation ! to denote factorial. 

Factorial of 5 = 5! = 5X4X3X2X1

Code

def findFactorial(num):

    try:

        a = int(num)

        value = a

        while a>1:            

            a = a-1

            value = value*a

            print(value)

        print("Factorial of {} is {}".format(num,value))

    except:

        print("You have not entered a number")

number = input("enter a number")

findFactorial(number)

Write a python program to check if a number is palindrome ?

 Palindrome

Definition of palindrome is any number/string which is same after getting reversed is palindrome.

Example - 123321, 131, GAG, EYE etc

Below is the python code to check if a string or number is palindrome

CODE

def check_palindrome(input_value):

    is_pallindrome = False

    letters_of_input = list(input_value)

    string_size = len(letters_of_input)

    start_index = 0

    end_index = string_size - 1

    mid_index = int(string_size/2) - 1

    while start_index <= mid_index:

        if input_value[start_index] == input_value[end_index]:

            is_pallindrome = True

        else:

            is_pallindrome = False

            break

        start_index = start_index+1

        end_index =end_index-1

        print(input_value[start_index],input_value[end_index])

    return is_pallindrome

input_data = input("Enter a number/string to check if its palindrome: ")

if check_palindrome(input_data) == True:

    print("The input value {} is palindrome".format(input_data))

else:

    print("The input value {} is not palindrome".format(input_data))

T-SQL LEAD LAG and SUM function based query

 

Query on T-SQL window clause

Below is the sales table

Order_Date	Name	Product	SubCategory	Category	Quantity	SalesAmount	DiscountAmount
10-08-2011	Megan Stewart	Mountain-100 Silver, 38	Mountain Bikes	Bikes	1	3399.99	0
14-09-2011	Elizabeth Clark	Mountain-100 Silver, 38	Mountain Bikes	Bikes	1	3399.99	0
14-10-2011	Olivia Torres	Mountain-100 Silver, 38	Mountain Bikes	Bikes	1	3399.99	0
20-10-2011	Ethan Foster	Mountain-100 Silver, 38	Mountain Bikes	Bikes	1	3399.99	0
14-11-2011	Beth Gutierrez	Mountain-100 Silver, 38	Mountain Bikes	Bikes	1	3399.99	0
14-11-2011	Dawn Wu	Mountain-100 Silver, 38	Mountain Bikes	Bikes	1	3399.99	0
20-12-2011	Desiree Dominguez	Mountain-100 Silver, 38	Mountain Bikes	Bikes	1	3399.99	0
22-12-2011	Kevin Wright	Mountain-100 Silver, 38	Mountain Bikes	Bikes	1	3399.99	0
17-01-2011	Clarence Rai	Mountain-100 Silver, 42	Mountain Bikes	Bikes	1	3399.99	0
23-01-2011	Elizabeth Johnson	Mountain-100 Silver, 42	Mountain Bikes	Bikes	1	3399.99	0
03-02-2011	Jaclyn Lu	Mountain-100 Silver, 42	Mountain Bikes	Bikes	1	3399.99	0
19-03-2011	Jon Zhou	Mountain-100 Silver, 42	Mountain Bikes	Bikes	1	3399.99	0
04-04-2011	Arthur Carlson	Mountain-100 Silver, 42	Mountain Bikes	Bikes	1	3399.99	0
07-04-2011	Adam Ross	Mountain-100 Silver, 42	Mountain Bikes	Bikes	1	3399.99	0
17-04-2011	Carolyn Navarro	Mountain-100 Silver, 42	Mountain Bikes	Bikes	1	3399.99	0
29-04-2011	Abby Sai	Mountain-100 Silver, 42	Mountain Bikes	Bikes	1	3399.99	0
05-05-2011	Ross Jordan	Mountain-100 Silver, 42	Mountain Bikes	Bikes	1	3399.99	0
06-05-2011	April Deng	Mountain-100 Silver, 42	Mountain Bikes	Bikes	1	3399.99	0
02-06-2011	Ebony Gutierrez	Mountain-100 Silver, 42	Mountain Bikes	Bikes	1	3399.99	0
08-06-2011	Julian Ross	Mountain-100 Silver, 42	Mountain Bikes	Bikes	1	3399.99	0

 test

• From the above table display all records along with sum of sales amount for each category.
• From the above table display all the records along with sales amount per month.
• Compare the sales between each day with previous sales value for each category.