Forum Discussion
Vba To power querry
Hello, please help me. I want convert this vba code for use on power querry. Please help me. Many thanks
Option Explicit
Public Function Trocar(Cat As String, Produto As String, PVP As Double, Rentab As Double) As String
Dim Lin As Long
Dim PVPLimite As Double
Dim PVPAtual As Double
Dim Trocou As Boolean
Trocou = False
Lin = 3
If Application.WorksheetFunction.CountIfs(Sheets("TABELA1").Range("B:B"), Produto) > 0 Then
Trocar = "Parceiro"
Exit Function
End If
Trocar = "NO SWITCH"
Cat = UCase(Cat)
PVPLimite = PVP + 1
PVPAtual = PVP
Do While Sheets("TABELA1").Cells(Lin, 1) <> ""
If UCase(Sheets("TABELA1").Cells(Lin, 1)) = Cat Then
If Sheets("TABELA1").Cells(Lin, 3) <= PVPLimite And Sheets("TABELA1").Cells(Lin, 4) > Rentab Then
Trocar = Sheets("TABELA1").Cells(Lin, 2)
Rentab = Sheets("TABELA1").Cells(Lin, 4)
PVPAtual = Sheets("TABELA1").Cells(Lin, 3)
Trocou = True
ElseIf Sheets("TABELA1").Cells(Lin, 3) <= PVPLimite And Sheets("TABELA1").Cells(Lin, 4) = Rentab And Trocou Then
If PVPAtual > PVP And PVPAtual > Sheets("TABELA1").Cells(Lin, 3) Then
Trocar = Sheets("TABELA1").Cells(Lin, 2)
Rentab = Sheets("TABELA1").Cells(Lin, 4)
PVPAtual = Sheets("TABELA1").Cells(Lin, 3)
ElseIf PVPAtual < PVP And PVPAtual < Sheets("TABELA1").Cells(Lin, 3) And PVP > Sheets("TABELA1").Cells(Lin, 3) Then
Trocar = Sheets("TABELA1").Cells(Lin, 2)
Rentab = Sheets("TABELA1").Cells(Lin, 4)
PVPAtual = Sheets("TABELA1").Cells(Lin, 3)
End If
End If
End If
Lin = Lin + 1
Loop
End Function
5 Replies
Don't waste your time trying to convert from one system (philosophy) to another (also called "fighting the API"). Please provide sample data in usable format (not as a picture) , show the expected outcome, and give sufficient context of what you are trying to achieve.
Hello, many thanks for your attention.
Please download the excel:
Imagine, I am a seller who wants to sell my products (Table 1 - My Products). So, I will analyze sales by category and I will try to exchange products sold to mine according to the following rules.
the rules is:
Switching between products in table 1 - my products for the sales table according to the rules
Exchange the sale product for my products if:
- Exchange made in the sales table. According to the rules, make sales exchanges for my products. It has to be in the same category
- "Price Limit - The price of the product that I will make the switch does not exceed one euro more"
- The profitability of the product that I will trade must have a profitability greater than or equal
- if it is a product in the table my products do not switch
- If the chances of exchange have the same profitability, the tiebreaker is for the price. Whoever has the smallest price difference for the sale price is chosen. However, if the products for exchange have the same profitability and the price difference is the same, the product with the lowest price is chosen.
Any doubt is just to say. Many many thanks
Please keep in mind that Power BI is a reporting app. While it has some limited support for "What if?" parameters it it not a fully featured modeling tool, and it also does not support data write back well. As far as I understand your process you probably want to look for a proper modeling application that is integrated with your other business processes.
in power query its possible? in vba i have.
Please help me
