Power BI and Excel daily report-CodePudding

I’m receiving a daily excel sheet report that contains daily incidents

My task is the following:

1- Consolidate the daily with the master sheet (including update the status of already existed TTs)

2- Calculate the SLA

3- Update charts for the TTs exceeded SLA

I was searching online to find a mechanism to automate the process by automate the merge using power automate then use power query to calculate the SLA finaly use power bi to visualise the data.

I would highly appreciate if anyone could advice me if my approach is right or to suggest another way.

Thanks a lot

CodePudding user response：

your tasks are not clear, which connections are you using or if you consolidate in excel or where-else ?

if you don't need to do anything in excel, did you try to refresh it on the server?

Go to Data Hub -->

CodePudding user response：

Your request seems a little vague, but, at least for the first part, it seems like you want to automate the process of automating worksheets. Check out the VBA script below. Get that working, then move on to step #2 and step #3.

Sub Combine()
'UpdatebyExtendoffice
Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "Combined"
Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")
For J = 2 To Sheets.Count
Sheets(J).Activate
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next
End Sub

https://www.extendoffice.com/documents/excel/1184-excel-merge-multiple-worksheets-into-one.html