Forum Discussion

henrique0galli's avatar
henrique0galli
Helper I
8 years ago
Solved

new query from table

How can I create a new query based on a generated table (based on other tables I had imported from Excel files).    
Need Help
  • henrique0galli's avatar
    henrique0galli
    8 years ago

    Would LOOKUPVALUE be the best function for this scenario?

     

    Thanks again

     

     

    **** SOLUTION UPDATE ****

     

    It was not able to create New Query based on the generated New Table. So managed to solve with this function:

     

    ColumNew = LOOKUPVALUE(tbl_Averages_as_tabular[Usage Forecasted],tbl_Averages_as_tabular[Works Type],tbl_Future_Allocations_Plannned[Works Type],tbl_Averages_as_tabular[Item],tbl_Future_Allocations_Plannned[Item])

  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    Hi henrique0galli

     

    This would be more efficient as a calculated column and I have attached a PBIX file to demonstrate

     

    New Column = 
    MINX(
        FILTER(
            'tbl_Averages_as_tabular',
            'tbl_Averages_as_tabular'[Item] = EARLIER('tbl_Future_Allocations_Plannned'[Item]) &&
            'tbl_Averages_as_tabular'[Works Type] = "Btoc"
            ),
            [Usage Forecasted])
henrique0galli's avatar
henrique0galli
Helper I
8 years ago

Hi Phil_Seamark, correct.

 

However, if I need to create queries using this generated table, would that be possible?

 

This generated table was done as: New Table = GENERATE('Table01','Table02')

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
8 years ago

Hi henrique0galli

 

Do you mean new queries to your source system?  Do you want to somehow use the rows in this table to generate a loop of queries?  Or something else?

  • henrique0galli's avatar
    henrique0galli
    Helper I
    8 years ago

    Hi Phil_Seamark,

     

    so I want to be able to Merge queries As New (based on the queries I already have), but also being able to merge this new table generated.

     

    How can I manipulate this generated table into the existing queries and merging them?

    • Phil_Seamark's avatar
      Phil_Seamark
      Microsoft Employee
      8 years ago

      oh, you can't "merge as new" as this table has been generated downstream of Power Query.

       

      But you can create new DAX calculated tables using the GENERATE function with filters.

       

       

      • henrique0galli's avatar
        henrique0galli
        Helper I
        8 years ago

        Hi Phil_Seamark,

         

        how could I use the function with filters?

        I now know how to create new tables using GENERATE, however how should I agregate the filters in the function?

         

        Rgs,