Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Employee
Employee

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


Anonymous
Not applicable

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
Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors