duranimal86
New Member
- Joined
- Jul 24, 2019
- Messages
- 18
- Office Version
- 365
- Platform
- Windows
I am making a Power BI dashboard and the data refresh of the power query has become unreasonably slow. The Source files are just 4 txt files (stored locally and less than 5MB total) which combined are less than 17k rows, so not large enough to cause major issues. The refresh was ok before, even including a calendar and a few other reference queries. But once i added the calculated column, the refresh became unbearably slow, taking hours to complete. The calculated column is in the main detail table and looks up the period name in the Calendar table and returns the corresponding date. I have tried multiple different formulas and query configurations and can't fix the issue. Is there something in my code that is causing this issue or is there something that can be added to fix this?
Also, every time i make any query change it refreshes from the source files all over again. Is that supposed to happen? I suppose it wouldn't matter if the refresh was quick, but it taking so long has made the file unusable. Any solutions or advice would be greatly appreciated. I am still fairly new to this and still have a lot to learn, especially how to optimize code.
Power Query:
let
Source = #"JE Detail Folder",
#"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Name], "HEADERS")),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"JE_HEADER_ID", Int64.Type}, {"LAST_UPDATED_BY", type text}, {"LEDGER_ID", Int64.Type}, {"JE_CATEGORY", type text}, {"JE_SOURCE", type text}, {"PERIOD_NAME", type text}, {"NAME", type text}, {"CURRENCY_CODE", type text}, {"STATUS", type text}, {"DATE_CREATED", type date}, {"ACCRUAL_REVERSAL_FLAG", type any}, {"ACTUAL_FLAG", type text}, {"DEFAULT_EFFECTIVE_DATE", type date}, {"CREATION_DATE", type date}, {"CREATED_BY", type text}, {"JE_BATCH_ID", Int64.Type}, {"POSTED_DATE", type date}, {"ACCRUAL_REV_EFFECTIVE_DATE", type any}, {"ACCRUAL_REV_STATUS", type any}, {"ACCRUAL_REV_JE_HEADER_ID", type any}, {"DESCRIPTION", type text}, {"ATTRIBUTE6", type any}, {"DOC_SEQUENCE_ID", type any}, {"DOC_SEQUENCE_VALUE", type any}}),
#"Inserted Text Before Delimiter" = Table.AddColumn(#"Changed Type", "Text Before Delimiter", each Text.BeforeDelimiter([CREATED_BY], "@"), type text),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Text Before Delimiter",{"Source.Name","LEDGER_ID","STATUS","CREATION_DATE","ACCRUAL_REV_EFFECTIVE_DATE","ATTRIBUTE6","DOC_SEQUENCE_ID","DOC_SEQUENCE_VALUE","CREATED_BY"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Text Before Delimiter", "CREATED_BY"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "PeriodEnd", each let
PERIOD_NAME = [PERIOD_NAME],
FilteredRows = Table.SelectRows(FiscalCalendar, each [AccountingPeriod] = PERIOD_NAME),
FirstRow = if Table.IsEmpty(FilteredRows) then null else FilteredRows{0}[PeriodEnd]
in
FirstRow
),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"PeriodEnd", type date}})
in
#"Changed Type1"Also, every time i make any query change it refreshes from the source files all over again. Is that supposed to happen? I suppose it wouldn't matter if the refresh was quick, but it taking so long has made the file unusable. Any solutions or advice would be greatly appreciated. I am still fairly new to this and still have a lot to learn, especially how to optimize code.
