Creating a virtual table with columns from different tables

[mixue100]

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

[v-cgao-msft · Accepted solution]

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!

[mixue100]

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 

[VahidDM]

HI @mixue100 

Can you share the sample of your data as text to be able to copy and past that? Which Product Columns should be in the visrtual table?

Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/