Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Instance Tracking

Good Morning 

 

I have an excel work book with a VBA i need to move to Power BI. The code allows me to calcuate Instances of Absence. 

The Data is now in Power BI, this there away i can use the VBA in BI?

 

Current Excel VBA:

 

Sub Instance()
Dim sRange As Range
Dim cel As Range
Dim nextS As Range
Set sRange = Range("G2:AAJ900")
For Each cel In sRange.Cells
If cel.Value = "S" And cel.Offset(, 1).Value = "RTO" Then
With sRange.Rows(cel.Row - sRange.Cells(1).Row + 1)
Set c = .Find("S", LookIn:=xlValues, LookAt:=xlWhole)
Set c = .FindNext(cel)
If cel.Row = c.Row Then
If Application.CountIf(Range(cel, c), "RTO") = (Range(cel, c).Columns.Count - 2) Then
Range(cel.Offset(, 1), c.Offset(, -1)) = "S"
End If
End If
End With
End If
Next
End Sub

2 Replies

  • You cannot use VBA in Power Query or Power BI. Recreate your code in M (the Power Query language) or DAX (the Power BI language)

  • v-robertq-msft's avatar
    v-robertq-msft
    Community Support

    Hi, Anonymous 

    According to your code, I think you can tell us about your logic and post some sample data(without sensitive data) and your expected result so that we can help you to achieve this in Power BI.

    Thank you very much!

    How to Get Your Question Answered Quickly 

     

    Best Regards,

    Community Support Team _Robert Qin

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.