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 calculat...
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.

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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        I am creating a Dim table by summarizing a few columns from Fact table. Using this Dim table in RLS as a link table. Though I can create the same table using a direct sql statement. But to reduce a db call, I am extracting it from Fact table. So, you are suggesting that a direct sql will be a better option ?