cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lotus99
Helper I
Helper I

refresh failed since June's update. with error: We cannot convert the value "[Table]" to type Table.

worry about monthly update: don't know what will be wrong again!

 

After June's update, a couple of scheduled refreshe failed, with error message: We cannot convert the value "[Table]" to type Table.. The exception was raised by the IDataReader interface

 

very simple just a merge caused issue. the merge is successful in Power BI desktop, only faied with refresh in service. 

the merge table is from a dataflow.

 

code:

Source = _filepath_POV_13_2021,
#"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "POV_PBI_Intl.TI")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Data"}),
#"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"EntityCode", "Year", "AccountCode", "CountryCode", "AdjustmentType", "Basket", "Subbasket", "FCAmount", "TaxAmount", "Date"}, {"EntityCode", "Year", "AccountCode", "CountryCode", "AdjustmentType", "Basket", "Subbasket", "FCAmount", "TaxAmount", "Date"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Data",{{"EntityCode", type text}, {"Year", Int64.Type}, {"AccountCode", type text}, {"CountryCode", type text}, {"Basket", type text}, {"Subbasket", type text}, {"FCAmount", Int64.Type}, {"TaxAmount", Int64.Type}, {"AdjustmentType", type text}, {"Date", type date}}),
#"Filtered <>DOMFTI" = Table.SelectRows(#"Changed Type", each ([AccountCode] <> "DOMFTI")),
#"Merged Queries" = Table.NestedJoin(#"Filtered <>DOMFTI", {"EntityCode"}, CHEV21, {"Entity Code"}, "CHEV21", JoinKind.LeftOuter)
in
#"Merged Queries"

 

CHEV21 is from dataflow, 

let
Source = PowerBI.Dataflows(null),
#"...-0f5e00a035f0" = Source{[workspaceId="...-0f5e00a035f0"]}[Data],
#"...-1a0ef9b18495" = #"...-0f5e00a035f0"{[dataflowId="...-1a0ef9b18495"]}[Data],
CHEV1 = #"...-1a0ef9b18495"{[entity="CHEV21"]}[Data]
in
CHEV1

 

 

thanks for help, those are critical business documentation, it worked for long time, now corrupted.

 

Celine ZHANG

1 ACCEPTED SOLUTION

Hi Selina,

 

it works again since yesterday afternoon (Pacific Time). 

 

thanks,

 

Celine ZHANG

View solution in original post

8 REPLIES 8
lotus99
Helper I
Helper I

Hi Selina,

 

Thanks for following up my issue.

 

It happens only with Power BI service, with Power BI desktop it works perfectly.

 

I created a very simple testing/debug dataset, only to test this issue, it happens all the time just with this merge, delete the merge line, no issue.

 

Thanks for helping, it’s disturbing our business process/reports.

Hi @lotus99 ,

 

I find the reason why the issue happens with line merged. 

 

For incremental refresh, it needs to meet some basic requirements, one of which is that the data source needs to be able to support query folder.  But I checked the limits of the  query folder and was surprised to find that merging would prevent it, as you did, if you performed the merge, the incremental refresh would fail, and canceling would work fine.

vmengzhumsft_0-1657075455945.png

 

The following is the related docs, limitations about query folder are in the first doc, you can refer it for more information, the second doc is about incremental refresh, including its process and basic requirements.

Query folding - Power Query | Microsoft Docs

Incremental refresh for datasets and real-time data in Power BI - Power BI | Microsoft Docs

 

Best regards,

Community Support Team Selina zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Hi Selina ,

 

thanks for the investigation. 

I don't perform incremental refresh, just regular simple refresh.

I'm puzzled from when to merge data, we need care about their source, need to be  directly accessed?

the previousely sliced and diced data done in dataflow, cannot be merged with Excel data from SharePoint? 

Capture.PNG

Hi @lotus99 ,

 

Did you succeed in refreshing in power bi desktop?

 

I  find one blog and the reason leading to your error is as the following says, so do you combine an external data source?

vmengzhumsft_0-1657173643062.png

And from your error, it says that your merge queries have something wrong, so may be you need to check your query.

In the following docs , there has related solution to solve this error, you can refer to it and try.

Power Query Errors: Please Rebuild This Data CombinationThe Excelguru Blog

If it doesn't work , you can think about putting every stages used for the query in a single BIG query.

Solved: Refresh problem - Unable to combine data - Microsoft Power BI Community

 

Best regards,

Community Support Team Selina zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Hi Selina,

 

it works again since yesterday afternoon (Pacific Time). 

 

thanks,

 

Celine ZHANG

Hi @lotus99 ,

 

Do you mean that your problem has been solved? If so, I am really very happy. Did it recover on its own or did you use some ways?

Would you be willing to tell me your method so that I can learn more?

 

Best regards,

Community Support Team Selina zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

 

 

Hi @lotus99 ,

 

Do you mean that your problem has been solved? If so, I am really very happy. Did it recover on its own or did you use some ways?

Would you be willing to tell me your method so that I can learn more?

 

Best regards,

Community Support Team Selina zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

 

 

v-mengzhu-msft
Community Support
Community Support

Hi @lotus99 ,

 

Have you tried to refresh your data in power bi desktop, does it refresh normally? What data source are you using?

 

If the issue only occurs on Power BI service, but not on Power BI Desktop, have you tried removing the entire dataset from Power BI service first, then republishing it from Power BI Desktop to see if it works?

 

Best regards,

Community Support Team Selina zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Solution Authors
Top Kudoed Authors