How to append calculated fields which is not in query editor

[Anonymous]

Hello,

I having some issues with some of my calculated fields when I am trying to append a few tables.

Firstly, I create some calculated fields in which I use the function "related" as some of the fields are in different tables. These calcualated fields are calculating a score for a KPI. This had to be done in 3 different tables which have different data.

Afterwards, I need to bring all my 3 tables (which have their own score KPI) into one single table in order to bring all 3 KPI from the 3 tables and calculate the average KPI of the three KPIs.

The issue is that I cannot perform my calculations in query editor (cannot use related function there), therefore when I am trying to append the three tables I am losing the calculated fields for KPI score.

Do you have any suggestions to resolve my issue??

Thanks in advance!

[v-lili6-msft]

hi, @Anonymous

Action in query editor is based on M language, and it is for the data source.

Action in data view of is based on DAX Function, and it is for data model.

 calculate column or calculate table are based on the DAX, so can be applied in the query editor.

There are usually two methods for you to refer to:

1. If the three tables have same columns and same column order, you use UNION to append the three tables.

2. You could use merge tables in query editor and then get the "related" to add a custom column, then append table in query editor.

If it is not your case, please share a simple sample pbix file and expected output.  Do mask sensitive data before uploading.

Best Regards,

Lin

[Anonymous]

Hello @v-lili6-msft,

Many thanks for your response. I will prepare a pbix file and send it to you soon. Before doing this I would like to know if there is the capability in Power BI to load multiple tables, then create some calculated fields on DAX and after this append all these tables into one single table which will include the calculated fields I created in each table before the tables' consolidation. 

Please see below an image where I have an example for you.

I am trying to append all 3 tables but I cannot have the calculated fields created in DAX into my consolidated table (i.e. in the example above I don't have the calc.1 and calc.2 in my consolidated table).

[v-lili6-msft · Accepted solution]

hi, @Anonymous

After my test, I'm afraid it is difficult to achieve in the modeling process.

It's better to do this in Edit Queries, 

If the calculate column could be done after appending query?

or use M language in Edit Queries to add a custom column instead of calculate column?

Could you please share some simple sample data with the true data structure and expected output. Do mask sensitive data before uploading.

Best Regards,

Lin