Combine a function with my code

[Yggdrasill]

Hi there

I have a function and I have a table.

My table needs to dynamically create a special text string based on what day it is today in the format YYYY/MM.

Here is that code:

let
    Today = DateTime.Date(DateTime.LocalNow()),
    #"1 year ago" = Date.AddDays(Today,-360),

    GeneratedList = List.Generate(()=>#"1 year ago", each _ < Today, each Date.AddMonths(_,1)),
    #"Converted to Table1" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Calculated Start of Month" = Table.TransformColumns(#"Converted to Table1",{{"Column1", Date.StartOfMonth, type date}}),
    #"Inserted Year" = Table.AddColumn(#"Calculated Start of Month", "Year", each Date.Year([Column1]), Int64.Type),
    #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([Column1]), Int64.Type),
    #"Changed Type2" = Table.TransformColumnTypes(#"Inserted Month",{{"Year", type text}, {"Month", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type2", "Custom", each if Text.Length([Month]) = 1 then "0"&[Month] else [Month]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Month", "Column1"}),
    #"Merged Columns" = Table.CombineColumns(#"Removed Columns1",{"Year", "Custom"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Dates"),
  in
    #"Merged Columns"

The output as of today is this:

Now I need to take each row and insert the text into an URL and get the results from each and every link like so:

WWW.WEBSITE.COM/[Dates] 

This is my function:

let
  Source = (Dates as text) =>

let
    Source = Json.Document(Web.Contents("https://www.website.com/"&Dates&"")), 
    rows = Source[rows]

in
    rows
in
  Source

When I invoke this function to my table it works fine - the Table code is actually longer. However, I can't refresh online because of this error

I've read through posts from Chris Webb and I've actually managed to work around this error previously but I'm stuck on this code.

I'm now trying to erase my function and instead doing all of this just inside my Table query.

Is that possible ?

[AllisonKennedy]

I am not sure if this is the same problem you are having, but I have had similar issues before with Web.Contents. Do you have a gateway for this dataset?
What links from Chris Webb have helped you? I know Web.Contents M function can refresh in some uses, and as I understand it, this depends on the credentials set up- if you have anonymous credentials to the Web.Contents you will need a gateway.
https://ideas.powerbi.com/ideas/idea/?ideaid=b8165080-cb6f-4c11-aa5c-f20604d4c349

https://docs.microsoft.com/en-us/power-bi/connect-data/power-bi-data-sources

[Yggdrasill]

Hello @AllisonKennedy 

This is the article I've used before with success sometimes

https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power...

[lbendlin]

Follow the advice in that blog post. Parameterize your web query, and use a fake standard URL if needed.

[Yggdrasill]

Thanks @lbendlin !

I did just now and this time PBI service is NOT giving me headache

I used this article by Chris Webb for the workaround: https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-i...

So basically I used RelativePath and Query inside the web.contents() function and voila ! No problems in PBI Service 

[lbendlin · Accepted solution]

Here's a script you can try.

let
    Today = DateTime.Date(DateTime.LocalNow()),
    #"1 year ago" = Date.AddYears(Today,-1),
    GeneratedList = List.Generate(()=>#"1 year ago", each _ < Today, each Date.AddMonths(_,1)),
    #"Converted to Table1" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom1" = Table.AddColumn(#"Converted to Table1", "Custom", each Date.ToText([Column1],"yyyy/MM")),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Custom"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom.1", each Json.Document(Web.Contents("https://website.com/" & [Custom])))
in
    #"Added Custom"

However your date format contains a forward slash "/"  and that will potentially conflict with your website's URL. May want to check the expected format again.

[Yggdrasill]

Ahh ! This works, I was almost there just not up to speed with the syntax rules : )

However, now I get this error in the PBI Service

Thank you for helping me out and solving the original dilemma - now onto the next one