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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
A9kurs
Frequent Visitor

Trying to pull column from another table

Hi Everyone,

I have two worsksheet in excel i am trying to show table in power bi.

 I need to bring Total cost from work sheet 2 into Worksheet 1 so that i can Achieve PO Issue * Total cost. I tried  Related table  it didnt work , I tried  to merge but getting error Oled db or Odbc error Please help. appreciate , thanks.

worksheet 1:

 

COOIDVendorCommodityCurrencyPO IssuePre ShipPost ShipDelivered
A1ACcAinCUSD0100%00
A2BArness/ActriclUSD0100%00
A3CFCrictionUSD30%070%0
A4DArness/ActriclUSD30%070%0
A5ECstCUSD30%70%00
A6FRuCCer RollsUSD30%70%00
A7GCcAinCUSD30%70%00
A8HFCrictionUSD30%70%00
A9IArness/ActriclUSD30%70%00
A10JAcACAtsUSD50%50%00
A11KAcACAtsUSD50%50%00
A12LTiresUSD50%50%00
A13MTrpsUSD50%50%00
A14NCstCUSD00100%0
A15OFCrictionUSD00100%0
A16PFCrictionUSD00100%0

 Work Sheet 2 :

IDTotal Cost 
1$10.00
2$11.00
3$12.00
4$13.00
5$14.00
6$15.00
7$16.00
8$17.00
9$18.00
10$19.00
11$20.00
12$21.00
13$22.00
14$23.00
15$24.00
16$25.00
1$20.00
2$21.00
3$22.00
4$23.00
5$24.00
6$25.00
7$26.00
8$27.00
9$28.00
10$29.00
11$30.00
12$31.00
13$32.00
14$33.00
15$34.00
16$35.00

 

 

Output :

 

COOIDVendorCommodityCurrencyPO IssuePre ShipPost ShipTotal Cost PO Issue * Total CostPre Ship * Total CostPost Ship * Total Cost
A1ACcAinCUSD0100%0$30.00$0.00$30.00$0.00
A2BArness/ActriclUSD0100%0$32.00$0.00$32.00$0.00
A3CFCrictionUSD30%070%$34.00$10.20$0.00$23.80
A4DArness/ActriclUSD30%070%$36.00$10.80$0.00$25.20
A5ECstCUSD30%70%0$38.00$11.40$26.60$0.00
A6FRuCCer RollsUSD30%70%0$40.00$12.00$28.00$0.00
A7GCcAinCUSD30%70%0$42.00$12.60$29.40$0.00
A8HFCrictionUSD30%70%0$44.00$13.20$30.80$0.00
A9IArness/ActriclUSD30%70%0$46.00$13.80$32.20$0.00
A10JAcACAtsUSD50%50%0$48.00$24.00$24.00$0.00
A11KAcACAtsUSD50%50%0$50.00$25.00$25.00$0.00
A12LTUSD50%50%0$52.00$26.00$26.00$0.00
A13MTrpsUSD50%50%0$54.00$27.00$27.00$0.00
A14NCstCUSD00100%$56.00$0.00$0.00$56.00
A15OFCrictionUSD00100%$58.00$0.00$0.00$58.00
A16PFCrictionUSD00100%$60.00$0.00$0.00$60.00
1 ACCEPTED SOLUTION
BrianConnelly
Resolver III
Resolver III

For Worksheet 2, create a summary table...

Summary Table = SUMMARIZE('Work Sheet 2',ID, "Total Cost",SUM([Total Cost]))

Then in Worksheet 1 use a lookup in a calculated column..

TotalCost = LOOKUPVALUE('Work Sheet 1'[ID],'Summary Table'[ID],[Total Cost])

 

View solution in original post

6 REPLIES 6
BrianConnelly
Resolver III
Resolver III

For Worksheet 2, create a summary table...

Summary Table = SUMMARIZE('Work Sheet 2',ID, "Total Cost",SUM([Total Cost]))

Then in Worksheet 1 use a lookup in a calculated column..

TotalCost = LOOKUPVALUE('Work Sheet 1'[ID],'Summary Table'[ID],[Total Cost])

 

Creted Summary Table.

 

But in Total cost formulae Summary table is not found and shows error.

TotalCost = LOOKUPVALUE('Work Sheet 1'[ID],'Summary Table'[ID],[Total Cost])

 

Can you share some screenshots?

Its working now Thank You so much. Appreciate your help.🙂

Great, could you mark my answer as solution?

Sure 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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