Power Query refresh taking HOURS after adding custom column

duranimal86

New Member
Joined
Jul 24, 2019
Messages
18
Office Version
  1. 365
Platform
  1. 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?

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.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top