Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

New calculated table in Power Query doesn't refresh if Source table is updated

Hi

I have created a new table in Power Query by merging(not by appending) two existing tables. But when the dataset is refreshed in Power BI Services, the source tables are updated but this calculated table doesn't and it keeps on showing the initial data which was present at the time of creation.

Any solution for this ?

*Everything is in import mode.

 

Thanks

Gori Shanker Suthar

Need Help
  • edhans's avatar
    edhans
    5 years ago

    Your FACT table is incomplete - it is part of an incremental refresh. It is, by definition, incomplete. Only the latest data is there after taht first refresh.

     

    You are not reducing a DB call either. Power Query works bottom up, not top down, so say you have this:

    1. Query 1 to SQL Server
    2. Query 2 to transform Query 1 to a good FACT table
    3. Query 3 based on Query 2 to filter your FACT table.

    Both query 3 and query 2 will go all the way back to Query 1 independently. Power query doesn't process Query 2 and then use hold the results of that for Query 3. It will redo Query 2, and consequently Query 1.

5 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    Right click on the merged query.  Is "Include in Refresh" checked?

    Pat

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Both "Enable load" and "Include in Report refresh" are ON for new table.
      I am using Incremental refresh on "Sales" table.

      Power Query of new table:
      let
          Source = Table.SelectColumns(#"Sales",{"CustName","CustAge","ProductID"}),
          joinTable = Table.NestedJoin(Source, {"ProductID"}, Products, {"ProductID"}, "Products", JoinKind.LeftOuter),
          finalTable = Table.ExpandTableColumn(joinTable, "Products", {"ProductName"})
      in
          finalTable

      The new table will have CustName, CustAge, ProductID, ProductName columns
      I create two cards, one for No. of rows in Sales and one for New table. The count is same at this moment. I publish the file and refresh the dataset on PowerBI Services. Now the count on Sales card is increased but the count of new table card is same.

      • edhans's avatar
        edhans
        Community Champion
        5 years ago

        You shoudn't create a DIM table from a FACT table that is part of an incremental refresh. Go back to the source sales table before incremental refresh, or create a second connection to the sales table for this. It won't hurt performance. Power Query will still make 2 calls to the server no matter how you do it.