Out of Memory Error - Managing multiple datasets

[dinoscool3]

So a bit of a weird problem herre. I have a large number of queries that I'm importing into power BI. Currently I have about 600 queries so far that I've merge into 12 tables. I disable load on all the other queries and just use the 12 tables. My final number of queries will be about 3000-4000. These queries are imports from a forum software called Discourse, I use a Blank Query that links with SQL data stored there.

I've been running into problems with memory, Power BI was running slow but now I can barely open the file and any time I try to refresh I get an Out of Memory error and it crashes. It seems to be overusing both memory and CPU. I'm currently using a Windows 365 with 4 core CPU and 16gb memory but I'm planning on upgrading to the 8 core and 32gb memory asap. But is there something else I can do to prevent these crashes or use less memory and CPU?

Here's an example of the query I use if that's helpful:

= (queryID) => let
        resultCount = 1000,
        otherNameForPage = 0,

        GetPage = (otherNameForPage) =>
            let
                content1 = "params={""page"":""" & Number.ToText(otherNameForPage) & """}",
                RawData = Json.Document(Web.Contents(
                    "https://test.com/admin/plugins/explorer/queries",
                    [RelativePath=Number.ToText(queryID) & "/run",
                        Query=
                        [
                            params="{""page"":""" & Number.ToText(otherNameForPage) & """}"
                        ],

                        Headers = [ 
                        #"api-username"="XXX",
                        #"api-key"="XXX",
                        #"Content-Type" = "application/x-www-form-urlencoded"],
                        Content = Text.ToBinary(content1)
                    ]
                    
                ) ),
                resultCount = RawData[result_count]
            in
                if RawData[result_count] = 0 then null else RawData,
            Pages = List.Generate(
                () => [i = 0, RawData = GetPage(i)],
                each [RawData] <> null,
                each [i=[i]+1, RawData = GetPage(i)],
                each Table.Combine(let raw = [RawData] in List.Transform(raw[rows], each Table.FromRows({_}, raw[columns])))),
            Output = Table.Combine(Pages)
in
    Output

  Basically it allows me to import SQL tables that are written in Discourse.

[parry2k]

@dinoscool3 I agreed, saving dataflow is painful and Microsoft is aware of it, and I hope it allows to save dataflow without validation, when that features comes out, it should be good. You are on the right path. Cheers!!

Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[parry2k]

@dinoscool3 it is pretty much PQ but in the cloud, you should be able to use it pretty easily, here is the link Introduction to dataflows and self-service data prep - Power BI | Microsoft Docs

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[parry2k · Accepted solution]

@dinoscool3 I would recommend using Power BI dataflow, processing the data in dataflow, and then using it in Power BI. I have worked with some complex/heavy transformations in the past where desktop just gave up but dataflow was awesome to work with.

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[dinoscool3]

I;ve marked this as accepted solution, because it is in a way but it seems Microsoft has a probelm with saving data flows. It takes forever for the query validation to happen. I've been sitting for 2 hours so far waiting for the import of my 800 tables to save. So it works, it just might be too slow right now unless Microsoft fixes it. 

Regardless, thanks! 

[dinoscool3]

Interesting, hadn't thought of that before. Any good documentation that I can look at to get started?