none
SSAS 2017 Get Data - Calculated columns disappear on editing Table query RRS feed

  • Question

  • I had created a table that merges data from multiple source queries and then imported it as a table and added calculated columns to it. 

    After that I needed to edit the query and merge a few more sources. So at this point, I have 2 options

    1. I see my query in the expressions pane so I can click edit expressions and edit my expression

    2. I see my table in the Tables section and I can right-click and go to Table properties and edit that query

    Which one should I do? Why is there an expression and a table? Can I delete the expression?

    Anyway, I chose the 2nd option, made my changes and clicked on Close & Update and now all the calculated columns I had added in the table are gone. 

    What did I do wrong.. or rather what is the correct way to edit the query of an imported table?

    Friday, October 27, 2017 12:03 PM

Answers

  • For the first question, if you want to edit the expression used to load data into the table itself, go through the table properties. "Edit Expressions" will not show you any of the Tables' partition expressions, and is used for editing expressions that are referenced either by other expressions, or themselves referenced by a table's partition query.

    An expression is like a function in M that can be called from other functions. The expression represents one of the tables that you pulled in from a data source, the query on the table object refers to that expression by name in order to merge the table with a second data source.

    You can delete expressions by right-clicking the expression in Tabular Model Explorer and selecting "Delete". The query editor is only a temporary view of the selected expressions for the purpose of editing, and we chose to ignore deletes that occur in that window, since that window does not make real-time changes to the Workspace Database.

    Hopefully that gives a little background and understanding.

    Now your question about calculated columns. We need to re-evaluate the schema of a query when you press save in order to update the schema of the AS Model to fit the new query. When doing so, as long as you have only one partition, we overwrite the existing schema with the new one we just found. A side effect of doing this causes all calculated columns to be removed. I've opened a change-request on our end to look into fixing this in a future release.

    Tuesday, November 7, 2017 8:40 PM