Forum Discussion

mim's avatar
mim
Icon for Advocate V rankAdvocate V
10 years ago
Solved

append the same table without double refresh

hi    is it possibe to append the same query without having to refresh both ?
Need Help
BhaveshPatel's avatar
BhaveshPatel
Icon for Super User rankSuper User
10 years ago

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's avatar
mim
Icon for Advocate V rankAdvocate V
10 years ago

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's avatar
    BhaveshPatel
    Icon for Super User rankSuper User
    10 years ago

    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's avatar
      mim
      Icon for Advocate V rankAdvocate V
      10 years ago

      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's avatar
        BhaveshPatel
        Icon for Super User rankSuper User
        10 years ago

        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.