Power Query refresh taking HOURS after adding custom column

[duranimal86]

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?

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.

 

[Matt Allington]

Many things here
1. Why are you adding this column in PQ? The data model is designed to be able to join the calendar to the data in the model.
2. It looks like a hand written step to add the column. Better to join the data table with the calendar and fetch the data that way. Better still, refer to 1
3. Yes, changes to queries will trigger a full refresh of that table

 

[duranimal86]

Hey Matt, I am hoping that there is a better way. A little more information, I have a list of transactions across different periods and i want to compare the date of each transaction to the Period End Date (from the Calendar) to get the number of days difference, and then have some grouping and reports based on the number of days. So i thought those two would have to be calculated on the transaction level in PQ, but if there is another way, that would be much better.

 

[Matt Allington]

You should build a star schema and load to the data model The Optimal Shape for Power BI Data

Join the transaction date to a calendar table using the date columns. Create calendar Tables in Power BI

Hide the date column in the transaction table and then use the calendar to slice and summarise your data. Make sure you have the period end date in the calendar table. You can add the number of days difference from the calendar date to the period end date to the cal table, too, if you like.