Sequel: How to make a variable in connection string? (dynamic Epoch in URL)

[Anonymous]

Hello forum,

I would like to come back to the post: https://community.powerbi.com/t5/Power-Query/How-to-make-a-variable-in-connection-string/m-p/73380/h... as I think there was no solution provided. At least it doesn't work for me. The parameter solution didn't work as it is static in a sense.

Please find the PBIX here.

If I place something like this into the "Yahoo finance" piece resp. URL part

Duration.TotalSeconds(Date.AddMonths(DateTime.FixedLocalNow(), -1) - #datetime(1970, 1, 1, 0, 0, 0))

I get this

Any idea how to make this work?

[Mariusz · Accepted solution]

Hi @Anonymous 

Try wrapping your calculation result in Text.From the error is due to mixed data types.

Text.From( Duration.TotalSeconds( Date.AddMonths( DateTime.FixedLocalNow(), -1 ) - #datetime( 1970, 1, 1, 0, 0, 0 ) ) )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

@Mariusz,

thank you, almost, I guess, the decimals are getting in the way. Do you know why?

[Anonymous]

I think I got it:

Text.From(Number.RoundDown(Duration.TotalSeconds( Date.AddMonths( DateTime.FixedLocalNow(), 0 ) - #datetime( 1970, 1, 1,0,0,0 ) ) ))

@Mariusz, thank you very much!