Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Why Custom Columns are missing from Edit Query view?

I'm created new column in data view with Modelling->New Column. However when I click "Edit Query" I don't see new column created.   This is issue for me, because I would like to do Append in Edit ...
Need Help
  • v-xicai's avatar
    v-xicai
    6 years ago

    Hi Anonymous ,

     

    Currently, the calculated column or calculated table which is created using DAX function won't be displayed in Query Editor.

     

    If you need to use a new column in Query Editor, you may go to Add column->Custom column, add a new custom column in Query Editor, see the similar case1 and case2. Then you may use Append feature to combine tables.

     

    If you would like to create column using DAX and need to combine tables into one like Append feature do, you can use function RELATED ,LOOKUPVALUE or FIRSTNONBLANK to get new calculated column, then use UNION function to get combined table.

     

    For example:

     

     

    Column1=RELATED（[one side field of relationship]）

Column1= LOOKUPVALUE(tableA[goal field],  tableA[linked field],  tableB[linked field]))

Column1= CALCULATE (FIRSTNONBLANK ( tableA[goal field], 1 ),FILTER ( ALL ( tableA), tableA[linked field] = tableB[linked field] ))
 

New Table1= UNION(
SELECTCOLUMNS('Table1',"Name1",[Description],"Name2",[Amount]),
SELECTCOLUMNS('Table2',"Name1",[Description],"Name2",[Amount]),
SELECTCOLUMNS('Table3',"Name1",[Description],"Name2",[Amount]))
 
New Table1= UNION(Table1,Table2,Table3)

     

     

    Best Regards,

    Amy

     

    Community Support Team _ Amy

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Is it only option to create column with Power Query if I want to use the column in Append?

    • MartynRamsden's avatar
      MartynRamsden
      Icon for Solution Sage rankSolution Sage
      6 years ago

      Anonymous 

       

      Yes, the column must exisit in Power Query to be used with Append.

       

      If it's not possible to create the column in Power Query, the you could create it in DAX and then create a calculated table using the UNION function to combine the 2 tables.

       

      Best regards,

      Martyn

       

      If I answered your question, please help others by accepting it as a solution.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        I create calculated column in data view, but problem is that I don't see those DAX based columns in "Edit Query" and columns are missing when I do Append.

         

        Who could help me? So I need to create column to table, which fetch value from another table. Then I need to append table to another table.