Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
mixue100
Frequent Visitor

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

mixue100_0-1644524070247.png

 

1 ACCEPTED SOLUTION
v-cgao-msft
Community Support
Community Support

Hi @mixue100 ,

 

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

Merge Queries as New.

vcgaomsft_1-1644820520416.jpeg

vcgaomsft_3-1644825216037.png

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

vcgaomsft_2-1644821254242.png

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!

View solution in original post

3 REPLIES 3
v-cgao-msft
Community Support
Community Support

Hi @mixue100 ,

 

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

Merge Queries as New.

vcgaomsft_1-1644820520416.jpeg

vcgaomsft_3-1644825216037.png

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

vcgaomsft_2-1644821254242.png

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
Frequent Visitor

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
Super User
Super User

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/

 

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.