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])
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,

  • henrique0galli's avatar
    henrique0galli
    Helper I
    8 years ago

    Hi again Phil_Seamark,

     

    so just to be more specific, this is what I want to do:

     

    I have these two tables:

     

    tbl_Future_Allocations_Plannned: this one was created from GENERATE function as you showed me previously.

    tbl_Averages_as_tabular: this one comes from a query.

     

    I want to have within table tbl_Future_Allocations_Plannned another column (let's say for example called `Demand`).

     

    To me more specific, consider as an example:

     

    item 4045/16 appears twice on table tbl_Averages_as_tabular. However, the number that should bring on Demand column created in table tbl_Future_Allocations_Plannned should bring the result from BtoC line (column Work Type), because the store name MORAYFIELD has it's Work Type called `BtoC` on table tbl_Future_Allocations_Plannned. 

     

    The result in this line on the recent Demand column created should be -2, correct? (it's a coincidence that all the values are -2 in this case, but that can varry.

     

    Please let me know if you could help me.

     

    Screen shot below for better understanding. 

     

     

     

     

     

    • henrique0galli's avatar
      henrique0galli
      Helper I
      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
        Microsoft Employee
        8 years ago

        Hi henrique0galli

         

        There are probably more efficient ways to do this that LOOKUPVALUE.  Can you cut and paste some sample data from these two tables into something I can replicate here and send you?