Forum Discussion

mixue100's avatar
mixue100
Frequent Visitor
4 years ago
Solved

Creating a virtual table with columns from different tables

Hi

 

I would like to create a virtual table by pulling out columns from different tables. Please see screenshot or attached for the sample.

How can I proceed to do so ? 

 

I tried cross join but cant seem to work 😞

 

Thanks 

M

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi mixue100 ,

     

    Maybe you can use Power Query Eidtor to create this table.

    Merge Queries as New.

    Remove the columns we don't need and  Enable load table to  the desktop.

    Attach the pbix file for reference.

    Hope it helps.

     

    Best Regards,

    Community Support Team_Gao

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

3 Replies

  • mixue100's avatar
    mixue100
    Frequent Visitor
    4 years ago

    actuals 

    Customer   Month       Revenue       Location       country       Product
    abd       Sep/21             50000             asia             china             22
    dec       Jan/21             20000             asia             malaysia             33
    ddd       Mar/21         10000             NAM             canada             55
    eee       Apr/21           5000             NAM             USA                   33
    abd       Jan/21         3000             EUR                   belgium             22
    ddd       Feb/21           2000             EUR             netherlands             11
    eee       May/21         1000             EUR                   netherlands             22

     

    target : 

    Customer Month       target       Product
    abd         Sep/21       60000       99
    dec         Jan/21       15000       33
    ddd       Mar/21       20000       55
    eee         Apr/21       10000       66
    abd       Jan/21       2500       22
    ddd       Feb/21       2000       88
    eee       May/21       500       22

     

    Virtual table to look like : 

     



    Customer  (actual)    Month(actual) Revenue(actual) target (target)  Product(actual) Location(actual) country(actual)
    abd                         Sep/21
    dec                         Jan/21
    ddd                        Mar/21
    eee                         Apr/21
    abd                        Jan/21
    ddd                        Feb/21
    eee                        May/21

     

     

    apologies for the alignment as they didnt allow me to paste the table nor attach a file 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi mixue100 ,

     

    Maybe you can use Power Query Eidtor to create this table.

    Merge Queries as New.

    Remove the columns we don't need and  Enable load table to  the desktop.

    Attach the pbix file for reference.

    Hope it helps.

     

    Best Regards,

    Community Support Team_Gao

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!