cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gsuthar
Regular Visitor

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

1 ACCEPTED SOLUTION

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

5 REPLIES 5
mahoneypat
Super User IV
Super User IV

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

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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.

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
gsuthar
Regular Visitor

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 ?

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors