Power Automate - Automatically fetch data from Power BI in to Excel and Send the copy of the Excel file via Email

Power Automate - Automatically fetch data from Power BI in to Excel and Send the copy of the Excel file via Email

Step-1
Step-2
Step-3
Using Command text we can make changes in the data we fetch from the Power BI.
Like add fields/columns, filter data, rename columns. It is a DAX formula.
Step-4
Step-5
    
Step-6
Step-7
Posting message on Teams.
We can post message to a Teams Group also by selecting Group chat from Post in and select the Teams Group from the Group chat dropdown that will appear after making selection.
Step-8
Location: We need to select the Group on SharePoint in which our file is located.
Document Library: We need to select the Documents option from the dropdown.
File: Select the file on which you want to run the script.
Script: Select the script to perform the desired action.
Step-9
Step-10
Step-11
Using Filter Query we can decide which file properties we want to get.
We are performing this step to attach excel as an attachment in email.
You can find more info in Step-11 ++
Step-12
Get Excel File to attach in email.
Step-13
Step-14
Here we are attaching excel file in email.
Step-15
Step-16

Step-11 ++
Filter Query - Partial Search

🛠 Filter Query Syntax for Partial Match:
substringof('partial_text', FileLeafRef)
Explanation:

substringof: Checks if the specified text exists in the target field.
partial_text: Replace this with the text you want to search for.
FileLeafRef: The internal name for the file name field in SharePoint.

🔍 Example 1: Search Files Containing "Report" in Name
substringof('Report', FileLeafRef)
This will match files like:

MonthlyReport.xlsx
Report_2025.pdf
Sales_Report_Q1.docx

🔍 Example 2: Search Files Containing "Invoice" in Any Part of Name
substringof('Invoice', FileLeafRef)
Matches:

Invoice_123.pdf
2025_Invoice_Summary.xlsx
InvoiceDetails.docx

🛑 Important Considerations:
Case-Sensitive: substringof is case-sensitive. For a case-insensitive search, you’ll need to adjust the query.
Spaces in Names: If your search text contains spaces, enclose the whole query in single quotes:
substringof('Sales Report', FileLeafRef)
No Wildcards: SharePoint OData doesn’t support * or % as wildcards. Use substringof instead.

🎉 Summary:
Use substringof('text', FileLeafRef) for partial name matches.
Replace 'text' with the part of the file name you want to find.
This should help you find files based on partial matches in Power Automate! 😊

Partial Search with File Type

🔍 Example 1: Search for Files Containing "Report" and Ending with .xlsx
substringof('Report', FileLeafRef) and endswith(FileLeafRef, '.xlsx')

🔍 Example 2: Search for Files Containing "Invoice" and Ending with .pdf
substringof('Invoice', FileLeafRef) and endswith(FileLeafRef, '.pdf')


Comments

Post a Comment

Popular posts from this blog

Function to Paste Excel Range on Outlook Mail Body (In text not image) Function Name - rngHTML()

Use the below function in your subroutine in the below mentioned way: .HTMLBody = str & rngHTML(Sheet1.Range("a13:g17"))  & .HTMLBody ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Function rngHTML(rng As Range)     Dim fso As Object, ts As Object, TempWB As Workbook     Dim TempFile As String     TempFile = Environ$("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"     '' copy the range and create a new workbook to paste the data into     rng.Copy     Set TempWB = Workbooks.Add(1)     With TempWB.Sheets(1)         .Cells(1).PasteSpecial Paste:=8         .Cells(1).PasteSpecial xlPasteValues, , False, False         .Cells(1).PasteSpecial xlPasteFormats, , False, False         .Cells(1).Select         Application.CutCopyMode = False         O...
Read more

Separate Text (Characters) & Numbers from Alpha Numeric String Using Formula and Macro (VBA)

Image
Have you ever come across the data where the text (characters) and numbers are put together in a single cell. Like Employee Name and Employee Code, Customer Name and Customer Number, etc. If the answer is Yes and you want to learn how to separate them using  formula  then this post is definitely for you. This type of data comes into three flavours: 1) Text and Number (Sujeet123) 2) Number and Text (123Sujeet) 3) Text and Numbers tangled (S1u2j3e4e5t6, 1s2u3j4e5e6t) We can separate the first 2 types using the formula and for 3rd type, we need to write a few lines of code (Macro). Let's start with the First one: 1) Text and Number (Sujeet123) Ex-1 Let's assume that  Cell A2  holds Sujeet123 in Sheet1. we will use the below formula with the Left and Right function to separate the Text and Number . =Min(find({01,2,3,4,5,6,7,8,9},A2&"0123456789")) The above formula will give us the starting position of the first Number. To find the Text Part  put the below...
Read more