Bharatanatyam

WriteBack Data from Power BI to Google drive Excel File through Power Automate

 

Prerequisites:

1.      The column data types in Power BI and the Excel file on Google Drive must match.

2.      Please ensure that you are logged into the connections used in the Power Automate flow.

3.      It is mandatory to create metadata in the Excel file on Google Drive.

Steps to create Power Automate flow:

 

Step 1:

 In Power BI Desktop, click on Power Automate. For reference, please see the screenshot below.

Step 2:

Once the Power Automate visual is opened in the screenshot above, click on the eclipse and

select 'Edit. For reference, please see the screenshot below

 

Step 3:

After clicking on Edit, you will be redirected to Power Automate. For reference, please see the screenshot below.

Step 4:

Click on New Flow and select Instant Cloud Flow. For reference, please see the screenshot below.

Step 5:

Once you click on Instant Cloud Flow, it will automatically create a Power BI Button Clicked action, as shown in the screenshot below.

Step 6:

            Click on Next Step and search for the Compose action, as shown in the screenshot below.

Step 7:

Once you click on Compose, it will prompt you for inputs as shown in the screenshot below. Then, click on Power BI Data.

Step 8:

1.     Click on next Step, Use the Google Drive connector in Power Automate.

2.     Choose "Google Drive" and select "Update File" or "Write to an Excel file".

3.     You will have to authenticate and connect your Google Drive account in Power Automate to access the files on your Google Drive.

 

Step 9:

After completing the necessary details, save the flow and run it as shown in the screenshot below.

Step 10:

               Check the output on Google drive file.

 

User Functions in ODI 12c

User functions can be used for reducing the complexity of code in mapping/procedures.

To avoid redundancy in mappings.
Re-usability and sharing code to other mappings

We can used in:

·         mapping

·         Procedure

·         Expressions

·         Filter component

·         Joiner component

Source table:

Here we have to create trim user function for all mapping,that is for project level.

Procedure:

·         Create source table and target tables same as above

·         Refresh the models using reverse engineering KMs

·         Follow the below steps to create user functions.

·         create user function in project

 

Enter the Name and group name of user functions

Enter the syntax which is function

TRIM($(P1))

Go to implementation tab and select add button (+)

Select the technology and logic with parameters.

TRIM($(P1))

Create Mapping for execute our function

Here we are using expression transformation

We must use trim user function on FIRST_NAME

Logic in expression component.

·         Use default IKM and LKM

·         Let’s EXECUTE

Result