Forum Discussion
Solved
Help translating VBA to Power Query
Hi, I want to create a dynamic query in Power BI. But I need to change my VBA in query to Power Query such that I can use it with a parameter My original MDX query contains this piece of VBA: ...
Figured out what I did wrong!
I used Power Query formula inside StrToMember (which expects VBA formulaes):
StrToMember("[Report Date].[Report Date].&[" + Str(Int(CDbl(Now()) - 38718)) + "]")So I changed this to:
[Report Date].[Report Date].&["& Value &"]
And then my original Value formula worked as I expected.
Value = Text.From(Int64.From(Number.From(DateTime.LocalNow()) - 38718))
But I don't know if this is the "best" way to do it, but it works
Figured out what I did wrong!
I used Power Query formula inside StrToMember (which expects VBA formulaes):
StrToMember("[Report Date].[Report Date].&[" + Str(Int(CDbl(Now()) - 38718)) + "]")So I changed this to:
[Report Date].[Report Date].&["& Value &"]
And then my original Value formula worked as I expected.
Value = Text.From(Int64.From(Number.From(DateTime.LocalNow()) - 38718))
But I don't know if this is the "best" way to do it, but it works