Forum Discussion

Brass Contributor

Sep 29, 2021

Solved

Power Query Append Repeatability Issue

I have a raw data source that has 350K rows. Each month around 25K-30K new lines are added. I have two tables of an old mapping and new (tables in G:H and J:K). I must show the result in a pivot (M:Q...

Example1.xlsx19 KB

excel

Formulas and Functions

SergeiBaklan
Sep 30, 2021
Jpalaci1

I'd avoid using of extra intermediate workbook. You may do everything in another workbook - combine two sources by Power Query, load result in data model (not to the table in the grid) and build PivotTable based on data model. Alternative land Power Query directly in PivotTable.

Sure, Access and even MS SQL Server with related tools are more powerful, but before shifting on them check again if you did all performance optimizations.

Chris Webb has series of posts around that Chris Webb's BI Blog: Optimising The Performance Of Power Query Merges In Power BI, Part 3: Table.Join And SortMerge Chris Webb's BI Blog (crossjoin.co.uk), you may find other advices.

SergeiBaklan

Diamond Contributor

Sep 30, 2021

Jpalaci1

I'd avoid using of extra intermediate workbook. You may do everything in another workbook - combine two sources by Power Query, load result in data model (not to the table in the grid) and build PivotTable based on data model. Alternative land Power Query directly in PivotTable.

Sure, Access and even MS SQL Server with related tools are more powerful, but before shifting on them check again if you did all performance optimizations.

Chris Webb has series of posts around that Chris Webb's BI Blog: Optimising The Performance Of Power Query Merges In Power BI, Part 3: Table.Join And SortMerge Chris Webb's BI Blog (crossjoin.co.uk), you may find other advices.

Jpalaci1

Brass Contributor

Oct 01, 2021

Thank you so much. As a temporary solution I dumped all completed data into a Access database and noticed my refresh time dropped dramatically. Refresh is only 49 seconds compared to the 5 1/2 to 6 minutes refresh via Excel.

I'll read into this link you provide. Thank you!