Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Yggdrasill
Responsive Resident
Responsive Resident

Combine a function with my code

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:

 

Yggdrasill_0-1598437295977.png


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

Yggdrasill_1-1598437548360.png


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 ?

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

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.

View solution in original post

6 REPLIES 6
AllisonKennedy
Super User
Super User

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

Please @mention me in your reply if you want a response.

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

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 

lbendlin
Super User
Super User

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

Yggdrasill_0-1598480004539.png


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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors