MSM: Loading error when trying to import data via power query template

(3) Share

Report

Posted on by Lea Kraus

165

Hi Team,

I am currently trying to perform data loads into the MSM via a Power Query template, but I always get following error:

Couldn't refresh the entity because of an issue with the mashup document MashupException.Error: Expression.Error: The column 'Column2' of the table wasn't found. Details: Reason = Expression.Error;ErrorCode = 10224;Detail = Column2;Microsoft.Data.Mashup.Error.Context = User

Within the Power Query I'm creating a few help tables to perform my data transformations. Those tables will not be imported into the MSM and should also not be loaded. But every time I open the connection or the template, the tables that should not be loaded are loaded anyway. The "Enable load" function cannot be turned off it seems.

I strongly suspect that the error occurs because the temporary table is tried to be loaded into the MSM...

Any idea on how to fix that?

Cheers,

Lea

Categories:

Sustainability Manager

I have the same question (0)

All responses (4)

Answers (1)

Suggested answer

Shivam Bansal 46 on at

Like (0)

Report

The error occurs because Power Query is trying to load temporary helper tables into Microsoft Sustainability Manager (MSM).

To fix this, open Power Query Editor and identify the helper tables. Use a workaround like appending a step to make these tables unreachable (e.g., Table.RemoveColumns(Source, {"NonExistingColumn"})). Ensure the "Enable Load" option is effectively bypassed, and confirm that all required columns in the main query are present and correctly named. Finally, test the connection after adjustments to ensure only the intended data is loaded into MSM.

Was this reply helpful? Yes No
Lea Kraus 165 on at

Like (0)

Report

Hi Shivam,

thanks for the suggestion. This actually seems like a good workaround but when trying it out we encounter another problem.

We need the output of the helper table for the main query. But when I block or bypass the loading of the helper table I cannot use its output anymore.

Do you know if it's possible to reference to a middle step of the query instead of the last step? I know that I can reference a previou step in the same query, but does it work for different queries?

Or any other idea on how to avoid that behaviour?

Thanks and cheers,

Lea

Was this reply helpful? Yes No
Verified answer

Lea Kraus 165 on at

Like (1)

Report

We actually found a weird, but feasible workaround for the "Enable Load" function within power query:

When creating the data import using the power query template, you can go through the steps as usual but it's important to set "Ready to import" at the end to No. So the query does not load immediately once you created it.

Next, you select the import and edit it again. In this step, you open the query editor and set "Enable Load" to No for all temp tables. Then you save it again and start the import and then it actually works. So with this workaround (first not loading the data with the template, then editing import and the query, then loading the data) the "Enable Load" function is turned off successfully.

Was this reply helpful? Yes No
HayesMoore 2 on at

Like (0)

Report

My problem is also solved, Thank you so much.

Was this reply helpful? Yes No