Space consumption issue and error-Urgent

[learner03]

I have recently started using Power BI and till now have basically created 4 reports. My data source is a custom connecter with .mez extension provided by the warehouse management system. I directly use this connector using Get Data option and it gives me below view-

I use the tables that i need and apply transformation steps in Power query and click apply.What I find is that it takes about an hour to upload data when I click Close and apply. 

Also, after creating 3 reports, when I clicked Close and apply for this 4th one, it showed me this below error after refresh for about 1 and half hour.

I do not know what is consuming space and how can I free up the space or why is this happeneing only after crating 3 basic reports. My Data load  looks like below-

My Laptop's specification are-

[Greg_Deckler]

@learner03 Well, could be an issue with the custom connector and for that you would need to contact your data warehouse vendor. How complex are your Power BI queries in Power Query Editor? How big is your PBIX? How many rows of data? Are you doing DAX calculated columns? I would turn off Auto date/time definitely if your data model is large, this will potentially save a ton of space. 

[learner03]

@Greg_Deckler 

How complex are your Power BI queries in Power Query Editor?

There are some transformation steps such as removing some coloumn, taking data starting from 1st July 2021, selecting only selcted peopl's name etc.

How big is your PBIX?

The one with this error s "memory is full" is 800 KB and another one is 4.7 MB.

How many rows of data?

One table has 40,000 rows, another one 1500 and the rest 3 have only 10 rows.

Are you doing DAX calculated columns?

Yes, I am using DAX calculated Column and they are criticIe for my measures that have created for visualisation.

Would turn off Auto date/time definitely if your data model is large, this will potentially save a ton of space.

Should I do that on Glabal level or current level and will doing so will affect my alrady created reports? 

[Greg_Deckler]

@learner03 My personal opinion based upon what you have shared is that this is an issue with that custom connector because the stats you are citing are insanely small for Power BI. That's just my opinion with no proof. Might be helpful if you posted your code from Advanced Editor for your queries so that we could really get a sense of the complexity but it would have to be stupidly complex for 40K rows to cause a problem for Power BI.

One thing you could do is to enable tracing in your diagnostics. Run the refresh, get the error and save the logs. Open a support ticket with Microsoft and see what they have to say. If you have a Pro account it is free. Go to https://support.powerbi.com. Scroll down and click "CREATE SUPPORT TICKET".

[learner03]

@Greg_Deckler 

Below is the code from the table that took the most time, around 1 and half hours and gave the error-

let
Source = dotWMS_PowerBI_Connector.Contents(),
H2 = Source{[Key="H2"]}[Data],
F1 = H2{[Key="F1"]}[Data],
T18 = F1{[Key="T18"]}[Data],
#"Invoked FunctionT1" = T18(null, null, "1/07/2021", null, null, null, null, null),
#"Filtered Rows" = Table.SelectRows(#"Invoked FunctionT1", each [Timestamp] > #datetime(2021, 7, 1, 0, 0, 0)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Message"}),
#"Filtered Rows1" = Table.SelectRows(#"Removed Columns", each [TransactionType] = "Pick"),
#"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",{"AdditionalInfo"}),
#"Filtered Rows2" = Table.SelectRows(#"Removed Columns1", each [TenantCode] = "SIMBA_RETAIL"),
#"Removed Columns2" = Table.RemoveColumns(#"Filtered Rows2",{"ReviewerEmail", "ReviewedTimestamp"})
in
#"Removed Columns2"

Also example of a measure is-

MTD =

VAR Transactions =

CALCULATE(

SUM('ULDs - History'[lines]),

DATESMTD('Calender'[Date])

)

VAR WorkingHours =

SUMX(

    SUMMARIZE(

        'ULDs - History',

        'ULDs - History'[Name],

        'ULDs - History'[Time Stamp(Date format)]

    ),

    CALCULATE(

        DATEDIFF(MIN('ULDs - History'[Timestamp]), MAX( 'ULDs - History'[Timestamp]), HOUR),

DATESMTD('Calender'[Date])

    )

)

RETURN

DIVIDE(

Transactions,

WorkingHours,

0

)

I will also do the Diagnostic thing as you said.

[Greg_Deckler · Accepted solution]

@learner03 Measures will not affect data load, only calculated columns. The code looks relatively innocent enough. You can use performance analyzer in Power Query Editor to perhaps see what row is taking the most time. Alternatively, duplicate your query and disable load on the duplicate. Then on the original, remove half the steps from the bottom and see if the issue persists. If so, remove another half of the steps. Otherwise, if the issue is gone, then add half of the steps you removed. In this manner, zero in on what is causing the problem.