append the same table without double refresh

[mim]

hi 

is it possibe to append the same query without having to refresh both ?

[v-haibl-msft]

@mim

If I understand you correctly, you can try the solution provided in following two links.

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/13305798-append-data-to-existing-...

https://social.technet.microsoft.com/Forums/en-US/9d0ff65a-59b8-435b-be0a-b9c6a72264a3/separate-quer...

According to this link, it seems that auto-refresh will always happen when using Power Query append function.

Best Regards,

Herbert

[mim]

@v-haibl-msft

thanks for the links, it is very helpful, my case is simpler ( I hope).

i have only one table and I need to duplicate that table, it seems if i append the same table, the refresh will be done two times, not sure if it is the case, as there is no easy way to profile powerquery.

maybe i will use Table.Repeat ?

sorry if i did not state my question clearly, so basicaly, i am happy to refresh everything, but i want PQ to do it only once not twice.

cheers 

[v-haibl-msft · Accepted solution]

@mim

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

[BhaveshPatel]

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.

[mim]

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

[BhaveshPatel]

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.

[mim]

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

[BhaveshPatel]

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.