Forum Discussion

jaryszek's avatar
jaryszek
Super User
1 year ago
Solved

Best practice to read tables from Web.Contents source

Hello,

how to set up web.contents 1 source query and read tables from it using referenced queries to not overload power bi? (not load web contents source multiple times).

The issue was described here:
https://blog.crossjoin.co.uk/2019/03/26/power-bi-caching-parallelism-and-power-query-refresh-performance/ 

anybody knows how to set up this?

 

Problem seems to be easy, but it is not...

 

Best,
Jacek

  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi jaryszek,

     

    Thank you for reaching out to Microsoft Fabric Community Forum.

     

    To avoid multiple Web.Contents calls in Power BI, create one base query with the Web.Contents call and reference it in other queries. Do not duplicate the call. Then, disable parallel loading of tables in Power BI (under Options > Data Load) to ensure the base query result is reused. This prevents redundant HTTP requests, as explained in Blog

     

    If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!


    Regards,
    Vinay Pabbu

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi jaryszek,

     

    Thank you for reaching out to Microsoft Fabric Community Forum.

     

    To avoid multiple Web.Contents calls in Power BI, create one base query with the Web.Contents call and reference it in other queries. Do not duplicate the call. Then, disable parallel loading of tables in Power BI (under Options > Data Load) to ensure the base query result is reused. This prevents redundant HTTP requests, as explained in Blog

     

    If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!


    Regards,
    Vinay Pabbu

    • jaryszek's avatar
      jaryszek
      Super User

      I made this one to Custom (not in image below but tested in power bi desktop):



      The source query is :

      let getRemoteRepository = (RepositoryName as text, CostMgmtPath as text, SpecificFolder as text, optional BranchName as text) => 
          let
              RefBranchName = if BranchName <> null then BranchName else null,
              Source = GitHub.Contents(RepositoryName, CostMgmtPath & SpecificFolder, RefBranchName),
      
              Files = List.Transform(Source, each [
                  Name = Record.Field(_, "name"), 
                  DownloadUrl = Record.Field(_, "download_url")
              ]),
      
              CsvFiles = List.Select(Files, each Text.EndsWith(Text.Lower([Name]), ".csv")),
      
              FilesTable = Table.FromList(CsvFiles, Splitter.SplitByNothing(), {"File Info"}),
              ExpandedTable = Table.ExpandRecordColumn(FilesTable, "File Info", {"Name", "DownloadUrl"}, {"Name", "Location"}),
      
              AddRelativePathColumn = Table.AddColumn(ExpandedTable, "RecordRelativePath", each 
                  let
                      a1 = Text.Replace([Location], paramBaseUrlRawGithub, ""),
                      a2 = Text.BeforeDelimiter(a1, "?token=", 0),
                      t = Text.AfterDelimiter([Location], "?token=", 0)
                  in
                      [
                          RelativePath = a2,
                          Token = t
                      ]
              ),
      
              ExpandedColumn = Table.ExpandRecordColumn(AddRelativePathColumn, "RecordRelativePath", {"RelativePath", "Token"}),
      
              AddColumnWithTable = Table.AddColumn(ExpandedColumn, "ResultTable", each 
                  let
                      a = Csv.Document(
                          Web.Contents(paramBaseUrlRawGithub, [
                              RelativePath = [RelativePath],
                              Query = [token = [Token]]
                          ]),
                          [Delimiter = ",", QuoteStyle = QuoteStyle.Csv]
                      ),
                      b = Table.PromoteHeaders(a, [PromoteAllScalars=true])
                  in
                      b
                  )   
          in 
              AddColumnWithTable
      in getRemoteRepository

      I  have 3 different source queries like above one. Poiting to different gitHub folders. 

      from this i am filtering tables to get a specific one and creating 20 seperated tables queries. 

      let GetCsvContentMart1Table = (tableName as text) =>
          let
              FilteredAllSourceTableUrl = Table.SelectRows(AllTables, each [Name] = tableName)[ResultTable]{0}
          in
              FilteredAllSourceTableUrl
      in GetCsvContentMart1Table


      Time of loading after disabling parallel uploading is increasing badly... why ?
      How to make the best set up when i have 3 different web contents (sharepoint lists, github etc.), after that I am merging them into 1 specific table and filtering to get seperated small csv tables from it? 

      What are the experts approaches? 
      Best,
      Jacek

       

      • Anonymous's avatar
        Anonymous
        Not applicable

        Hi jaryszek,

         

        Disabling parallel loading forces Power BI to load each table sequentially, which slows down performance.


        Look at the below doc - This approach aligns with creating base queries per source and combining them into a merged query.

        https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-shape-and-combine-data 

         

        If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!


        Regards,
        Vinay Pabbu

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi jaryszek,

     

    As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
    If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

     

    Regards,
    Vinay Pabbu

    • jaryszek's avatar
      jaryszek
      Super User

      please do not cose the case i want to hear also opinion some power bi experts here. 

      • Anonymous's avatar
        Anonymous
        Not applicable

        Hi jaryszek,

         

        May I ask if you have gotten this issue resolved?

        If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.


        Regards,
        Vinay Pabbu