Forum Discussion
Web.Contents with dynamic file URL
I've got it working. All ive done is set all URL's as parameters, I also added a space between parameters and the & in the functions. All minor stuff so this makes me think there is more to it.
Anyway I uploaded it to my service updated the facebook file and refreshed and the clicks updated.
I've updated the pbix with your sharepoint path, here are the files again.
What I suggest is you share a public sharepoint or dropbox link with the files in. Im certainly not hthe most skilled person on this forum. Although Im up for the challenge you dont want to exclude the rest of the community.
What are the data sources of the three other files?
Previously ive had issues calling functions in other queries. What I did was combine everything into one query, it may be a security issue which wont show on Desktop.
e.g. I have a query that iterates through the pages of an API that limits the repsonses to 1000 records. When I had the functions seperatley the Service would not refresh. Desktop would. Combining them sorted it.
let
MemberPages =
let
Source = Json.Document(Web.Contents("https://xx.xxx.com/api/xxx/xxx?size=1000")),
TotalPages1 = Source[TotalPages]
in
TotalPages1,
GetMembers = (pPage as text) =>
let
gBaseUrl = "https://https://xx.xxx.com/api/xxx/xxx",
gMaxSize = 1000,
vOptions = [Query=[size=Text.From(gMaxSize), page=pPage]],
Source = Json.Document( Web.Contents ( gBaseUrl, vOptions ) ),
Records = Source[Records],
#"Converted to Table" = Table.FromList(Records, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", REMOVED!!!})
in
#"Expanded Column1",
Source = List.Generate(() => 1, each _ <= (MemberPages), each _ + 1),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Start"}}),
#"Invoked Custom Function" = Table.AddColumn(#"Renamed Columns", "Query", each GetMembers([Start])),
#"Expanded Query" = Table.ExpandTableColumn(#"Invoked Custom Function", "Query", {REMOVED !!!),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Query",{"Start"})
in
#"Removed Columns"
Hello aTChris,
Thank you first of all for the reminder of making environment public, however, I just tried opening a new team but maximum allowance in my organization can only be public within the organization, I cannot make it open to public...
Following is my google drive with folder structure and the pbix file.
https://drive.google.com/drive/folders/1zo8gqyDfqUxKvFqtagW0YgZFd8HZUpGQ?usp=sharing
In Power Query, you can ignore all the parameters in User Inputs folder. Then modify the sharepoint site URL of the following 3 queries:
Consolidated Media Raw Data
FACEBOOKProcessor
YOUTUBEProcessor
As there are many different excel formats from all of our media vendors, I create 1 processor to alter 1 excel into our standard format. In this sample I leave 2 processors: Facebook and Youtube.
Consolidated Media Raw Data is the main query. It starts with Sharepoint.Files to list all files of a site. After some filtering I will get the files what I want to process. Then at "Invoke Custom Function", according to the folder name, file name with folder path will be passed to corresponding processor for format alteration.
This pbix will work in Desktop, but when loading to BI Service it won't due to Web.Contents used in those 2 processors.
Thank you so much!
Best regards,
David