Which function would work best when using a power query?

T

Tmcgrew05

New Member
Joined
Oct 29, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows
I am using Excel 365. I am new to Power Queries but was able to get the information I wanted into a nice table. Now, I am wanting to get that information into the nice spreadsheet for daily analysis for my boss. If this is actually possible. I was using two workbooks and had #ref errors when my boss opened file (which is from D6 on) (probably due to shared license?) so, I had bright idea to try to pull my report in from our SQL that will dump into my power query daily and add to my data table. My problem is how would I pull the values on each specific date on the specified tabs from my power query (each tab is supposed to represent each day of the month)? Then I have a summary tab. I have tried sumif, sumifs, dsum, and dget. The sumif worked before on the first one but the next tab will pull both days. I'm pretty sure I could use (-) minus 1 day as part of my formula to do each day thereafter, correct? I just am not sure how to put it all together. Also, could I not automate this daily routine with a VBA code? The 1st one is tab 1/day 1(worksheet 1) where the Earned Value needs to end up from the second table which is my power query tab. I hope this all makes sense.


New Daily Summary with MTD Rev A (1) (version 1).xlsm
ABCD
1Work CenterSUPVDescDate
22022-02-01
3FEC 2BAY 1985B/241B/881A/051A LENS-BEZEL ASSY#VALUE!
4FFV 1BAY 1010B CHL AFS/HG/SPORT, LH/RH
5FGB 1BAY 1985B/241B Base HL
6FSA FGB 2BAY 1985B/241B REFL B SUBASY$ 3,698
7
8FAQBAY 2150A LH FOG$ 1,437
9FAQ 2BAY 2150A RH FOG$ 1,590
10FBD 2BAY 2AUTOMATED PES FL$ 167
11FFN ABAY 2THRA/T6ZA/TGSX FTSL, RH$ -
12FFN BBAY 2THRA/T6ZA/TGSX FTSL, LH$ -
13FGH 1BAY 2P375 HL,LH$ 3,299
14FGH 2BAY 2P375 HL,RH$ 3,528
15FGQ 1BAY 2U540(2)/U540 LENS/BEZEL SUB$ -
16
17FEB ABAY 3010B HL BASE,LH$ 4,478
18FEB BBAY 3010B HL BASE,RH$ 4,619
19FSA FFV 3ABAY 3K3/K4 Sub Assy, 2 Up$ 3,659
20FSA FFV 3BBay 3K3/K4 Sub Assy, 2 Up$ 3,614
21FSA FFV 4ABAY 3K3/K4 LED SUBASSY, 1-UP/2-UP$ 3,077
22FSA FFV 4BBAY 3K3/K4 LED SUBASSY, 1-UP/2-UP$ 762
23FSA FFV 5ABAY 3K5 LH PES Sub Assy$ 731
24FSA FFV 5BBAY 3K5 RH PES Sub Assy$ 977
25FSA MOLD 1BAY 3LENS/BEZEL SUBASSY,010B HL,BASE GRADE,LH$ 2,420
26FSA MOLD 2BAY 3LENS/BEZEL SUBASSY,010B HL,BASE GRADE,RH$ 2,709
27FSA MOLD 3BAY 3LENS/BEZEL SUBASSY,010B HL,HIGH GRADE,LH/RH$ 3,525
28
29FANBAY 4PES GLASS SUBASSY$ 2,527
30FEN ABAY 4440A RRR/FLR, LH$ -
31FEN BBAY 4440A RRR/FLR, RH$ -
32FGP 1BAY 4U540(2) MCA HL LH/RH$ -
33FHX 1BAY 4BT1FG HL SUV$ -
34FHX 3BAY 4BT1FG PES SUBASSY$ 463
35FHX 4BAY 4BT1FG BRKT A SUB ASSY$ 335
36FHX 5BAY 4BT1FG FCL LENS/BEZEL SUBASSY$ 451
37FHX 6BAY 4BT1FG CHL LENS/BEZEL SUB ASSY$ 446
38
39FHZ 1BAY 5BT1FG CDHL Main$ -
40FHZ 2BAY 5BT1FG CDHL Sub Assy$ -
41FSA FFP 4BAY 5PES UNIT SUBASSY,U540 GDHL$ -
42
43FETBAY 6C489 DRL LH/RH / U540(2) FTSL$ -
44FFP 2BAY 6U540/P375 DRL SUBASSY LH/RH$ 1,308
45FFP 3BAY 6U540 HI/LO LED SUBASSY/U540(2) HI LED SUBASSY$ 489
46FGNBAY 6U540(2) HL LED LO$ -
47FGO 1BAY 6U540(2) MCA HI/LO DRL$ -
48FHV 1BAY 5GLOBAL FOG MANUAL LINE$ 2,174
49
50FHD 1BAY 7TYAA HL LED,LH/RH$ 4,570
51FHD 2BAY 7HONDA PES SUB UNIT B,RH$ 1,765
52FHD 3BAY 7HONDA PES SUB UNIT B,LH$ 1,704
53FHD 4BAY 7TYAA PES/BRACKET SUBASSY$ 2,068
54FHD 5BAY 7TYAA Housing Sub Assy$ 2,274
55FHF 1BAY 7TGVA HL LH/RH$ 1,737
56FHF 2BAY 7HONDA PES SUB UNIT A LH$ 4,090
57FHF 3BAY 7HONDA PES SUB UNIT A RH$ 3,255
58FHF 4BAY 7TGVA PES/BRACKET SUBASSY$ 1,054
59FHF 5BAY 7HOUSING SUBASSY,TGVA CHL,LH/RH$ 1,272
60FHT 1BAY 7U725 HL Lo Series LH/RH$ 8,922
61FHT 10BAY 7U725 High Series Print Base Sub-Assembly LH$ -
62FHT 12BAY 7U725 HL High Series RH$ -
63FHT 18BAY 7U725 High Series Cover A Sub Assembly RH$ -
64FHT 2BAY 7U725 HL High Series LH$ -
65FHT 20BAY 7U725 High Series Print Base Sub-Assembly RH$ -
66FHT 3BAY 7U725 HI/LO BEAM SUB ASSY,LH$ 2,379
67FHT 4BAY 7U725 HI/LO BEAM SUB ASSY,RH$ 2,303
68FHT 5BAY 7U725 HL Lo Series Lens Assembly LH/RH$ 971
69FHT 6BAY 7U725 HL High Series Lens Assembly LH/RH$ -
70FHT 7BAY 7U725 Lo Series Extension A Sub Assembly LH/RH$ 1,180
71FHT 8BAY 7U725 High Series Cover A Sub Assembly LH$ -
1
Cell Formulas
RangeFormula
B50:B71,B43:B48,B39:B41,B29:B37,B17:B27,B8:B15,B3:B6B3=+VLOOKUP($A3,Table1,2,FALSE)
C50:C71,C43:C48,C39:C41,C29:C37,C17:C27,C8:C15,C3:C6C3=+VLOOKUP($A3,Table1,3,FALSE)
D3D3=SUMIFS(Query!$A$1:$C$45,Query!B2,Query!$C$1:$C$45,A1,Query!$C$1:$C$45)
D6D6=SUMIF(Table1_2,A6,Table1_2[SUM_EARNED])
D8:D15,D50:D71,D43:D48,D39:D41,D29:D37,D17:D27D8=SUMIF('Report Daily Dump.xlsx'!Table1[Work Center],A8,'Report Daily Dump.xlsx'!Table1[Earned Value])
Press CTRL+SHIFT+ENTER to enter array formulas.
Named Ranges
NameRefers ToCells
Query!ExternalData_1=Query!$A$1:$C$45D3



New Daily Summary with MTD Rev A (1) (version 1).xlsm
ABC
1Work CenterProd DateSUM_EARNED
2FBD 22/1/2022167.0856
3FAN2/1/20222526.9746
4FSA FFV 4A2/1/20223076.504
5FFV 12/1/20221747.1454
6FFP 32/1/2022489.3056
7FHD 42/1/20222068.4802
8FHD 52/1/20222273.832
9FHF 52/1/20221271.5164
10FHX 52/1/2022451.344
11FHX 42/1/2022334.5712
12FHX 62/1/2022445.7022
13FEC 22/1/20222332.4038
14FGH 22/1/20223528.19
15FEB B2/1/20224618.9143
16FHX 12/1/20220
17FAQ2/1/20221437.4272
18FSA FFV 3A2/1/20223659.4064
19FSA FFV 3B2/1/20223613.8708
20FHF 42/1/20221054.1025
21FHF 22/1/20224090.374
22FSA MOLD 32/1/20223525.0553
23FSA MOLD 12/1/20222419.651
24FSA FGB 22/1/20223697.7081
25FHT 72/1/20221180.1088
26FHT 42/1/20222302.8912
27FHT 32/1/20222379.4416
28FHF 12/1/20221736.8245
29FSA FFV 5A2/1/2022731.4216
30FHD 32/1/20221704.3225
31FHF 32/1/20223254.8035
32FSA MOLD 22/1/20222709.052
33FHT 52/1/2022970.6851
34FSA FFV 5B2/1/2022976.5684
35FGH 12/1/20223298.5624
36FEB A2/1/20224478.167
37FHX 32/1/2022463.3783
38FGB 12/1/20223622.92
39FHV 12/1/20222174.4632
40FHT 12/1/20228921.8936
41FHD 12/1/20224570.0603
42FFP 22/1/20221308.2332
43FAQ 22/1/20221590.4128
44FSA FFV 4B2/1/2022762.32
45FHD 22/1/20221764.6525
Query
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
Why do you not load your data into a pivot? No formula required.
 
Upvote 0
And what might be the reason for that?
My boss does not like Excel, yet he trusts me to deliver the right insights through whatever object I believe is best suited: (power)pivots, charts, tables.
 
Upvote 0
Well, simply because that is not what he wants to look at. He has spreadsheet and that's the way he wants it.
 
Upvote 0
Your table presented using XL2BB shows a link to another workbook which results in an error when I try to load it to my machine to work and test. Suggest you only load hard coded data so that we can manipulate the data.
 
Upvote 0
alansidman said:
Your table presented using XL2BB shows a link to another workbook which results in an error when I try to load it to my machine to work and test. Suggest you only load hard coded data so that we can manipulate the data.
Click to expand...
My apologies. I'm trying to get away from the other workbook therefore I done the power query inside this workbook.
 
Upvote 0
I guess I wasn't clear enough. If you expect some help using the XL2BB file, you should reupload it without the links so that we can copy and paste and run some scenarios.
 
Upvote 0
I have deleted the links to the other folder. Does this help?



Book1
ABCD
1Work CenterSUPVDescDate
23/1/2022
3FEC 2BAY 1985B/241B/881A/051A LENS-BEZEL ASSY
4FFV 1BAY 1010B CHL AFS/HG/SPORT, LH/RH
5FGB 1BAY 1985B/241B Base HL
6FSA FGB 2BAY 1985B/241B REFL B SUBASY
7
8FAQBAY 2150A LH FOG
9FAQ 2BAY 2150A RH FOG
10FBD 2BAY 2AUTOMATED PES FL
11FFN ABAY 2THRA/T6ZA/TGSX FTSL, RH
12FFN BBAY 2THRA/T6ZA/TGSX FTSL, LH
13FGH 1BAY 2P375 HL,LH
14FGH 2BAY 2P375 HL,RH
15FGQ 1BAY 2U540(2)/U540 LENS/BEZEL SUB
Sheet1
Cell Formulas
RangeFormula
B3:B5B3=+VLOOKUP($A3,'[KPI manning Rev 2.xlsm]WC_INFO'!$A$2:$C$70,2,FALSE)
C8:C15,C3:C6C3=+VLOOKUP($A3,'[KPI manning Rev 2.xlsm]WC_INFO'!$A$2:$C$70,3,FALSE)
B6,B8:B15B6=+VLOOKUP(A6,'[KPI manning Rev 2.xlsm]WC_INFO'!A2:C70,2,FALSE)



Book1
ABC
1Work_CenterProd_DateEarned_Value
2FBD 23/1/2022501.2568
3FEB B3/1/20227523.8978
4FFV 13/1/20221940.4482
5FGB 13/1/20223353.3836
6FGN3/1/20221894.7309
7FGP 13/1/20225722.4805
8FGQ 13/1/202254.2294
9FHD 23/1/20221104.039
10FHD 53/1/20224005.561
11FHF 43/1/2022843.282
12FHF 53/1/2022892.8009
13FHT 123/1/20225588.6512
14FHT 203/1/20221070.012
15FHT 33/1/20225156.2704
16FHT 73/1/2022626.9328
17FBD 23/2/2022751.2523
18FEB B3/2/20227370.2835
19FFV 13/2/2022830.5008
20FGB 13/2/20224767
21FGP 13/2/20224557.715
22FGQ 13/2/202228.2936
23FHD 23/2/2022446.442
24FHD 53/2/20223306.8466
25FHF 43/2/2022919.944
26FHF 53/2/2022926.4915
27FHT 123/2/20225196.06
28FHT 203/2/20221461.935
29FHT 73/2/2022696.0798
30FSA MOLD 33/2/20225920.8783
Sheet2
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
Trying to use a formula in one workbook to sum data of multiple rows with duplicate names in another workbook. Getting reference error.
Replies
3
Views
577
Tmcgrew05
T

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top