Forum Discussion
append the same table without double refresh
I found following two articles which introduce the method of analyzing the Power Query trace log. Maybe it can help to check the refresh times.
http://excelando.co.il/en/analyzing-power-query-performance-source-large-files/
https://blog.crossjoin.co.uk/2014/12/11/reading-the-power-query-trace-filewith-power-query/
Best Regards,
Herbert
Yes. You can append the multiple queries in PowerBI without the refresh. It is either the same query or the new query created with the other data source.
here is an example
let
Source = Excel.Workbook(File.Contents("C:\Users\mimoune.djouallah\Desktop\tar\TAR.xlsx"), null, true),
COMBINED_Sheet = Source{[Item="COMBINED",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(COMBINED_Sheet),
#"Appended Query" = Table.Combine({#"Promoted Headers", #"Promoted Headers"})
in
#"Appended Query"
as i have no way to know how powerquery internally works, are steps, source, COMBINED_Sheet, #"Promoted Headers" are done only 1 time ?
cheers
All the query processing steps are recorded like VBA Macro in powerquery. If you would like to change something in previous step, You can do so by deleting particular step and recreating a new step. It is really flexible and smart tool which automates the certain tasks such as data type detection, promoting headers automatically but if you want to manually override those steps, you can go to the particular step and select the gearbox icon next to it to see how it is processing steps internally.
You can also write custom queries in PowerQuery using Advanced Editor in the View Tab.
let's put it in a different way in this query
let
Source = Excel.Workbook(File.Contents("C:\Users\mimoune.djouallah\Desktop\tar\TAR.xlsx"), null, true),
COMBINED_Sheet = Source{[Item="COMBINED",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(COMBINED_Sheet),
#"Appended Query" = Table.Combine({#"Promoted Headers", #"Promoted Headers"})
in
#"Appended Query"when i hit refresh does powerquery call this step "Source" one time or two time ?
note: i am familiar with powerquery, i have a query that fetch 2Millions records and do all kind of transformation, but it become quite slow, nearly 15 minutes to finish, so i am trying to fine tune a better solution
It is fetching the data from scratch everytime you hit refresh. If you would like to fetch just newly added rows, You can use partioning in SSAS Tabular which will only fetch the newly added data. This is the negative of PQ. However, If you use PowerPivot, It is faster to load data using powerpivot get data option.