Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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:
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingRight click on the merged query. Is "Include in Refresh" checked?
Pat
To learn more about Power BI, follow me on Twitter or subscribe 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingI 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:
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.