Forum Discussion

learner03's avatar
learner03
Post Partisan
5 years ago
Solved

Space consumption issue and error-Urgent

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 ...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    5 years ago

    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.

learner03's avatar
learner03
Post Partisan
5 years ago

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's avatar
Greg_Deckler
Community Champion
5 years ago

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.