Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 ?
Solved! Go to 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.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
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...
Follow the advice in that blog post. Parameterize your web query, and use a fake standard URL if needed.
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
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.
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