Selecting a field

P

Paul_Christie

New member
Joined
Mar 23, 2016
Messages
153
Reaction score
0
Points
0
Location
Nottingham, UK
Website
www.instantpages.ltd.uk
Excel Version(s)
Office 365 Monthly update
I've got a table that looks like this

attachment.php


and I've created a merge with another table that looks like this

ValueBMU IDSPTriadSP31SP32SP33SP34SP35SP36SP37SP38SP39SP40TSP
18/01/20162__BAVRO000SP 35Triad20000000000[SP35]
18/01/20162__DAVRO000SP 35Triad20000000000[SP35]
18/01/20162__EAVRO000SP 35Triad20000000000[SP35]
18/01/20162__FAVRO000SP 35Triad20000000000[SP35]
I'm trying to create a third table where I've got the Date(Value) column, the BMU ID column and the appropriate value from the column referred to by the TSP column.

Has anyone got any ideas how I might achieve this. I'm fairly new to PQ but I've figured out enough from Ken's book to populate the TSP column via his pqVLOOKUP. I'm hoping there is some functiomn that I'm not yet aware of that will give em something like Field(TSP).value.

Hoping someone can help me with this.
 

Attachments

  • Image2.jpg
    Image2.jpg
    27.2 KB · Views: 69
Paul, can you provide a pic of the outcome you're expecting here? I'm not quite clear on how the date/SP work in this case... I.e. which are the appropriate columns?
 
Ken

This

ValueBMU IDSPTriadSP31SP32SP33SP34SP35SP36SP37SP38SP39SP40TSPTSP Value
18/01/20162__BAVRO000SP 35Triad20000000000[SP35]
18/01/20162__DAVRO000SP 35Triad20000000000[SP35]
18/01/20162__PAVRO000SP 35Triad20000000000[SP35]
18/01/20162__AAXIS000SP 35Triad20.04980.0490.04860.04170.02790.02260.02420.01670.01080.0103[SP35]0.0279
18/01/20162__BAXIS000SP 35Triad20000000000[SP35]
18/01/20162__CAXIS000SP 35Triad20000000000[SP35]
18/01/20162__DAXIS000SP 35Triad20.01350.01250.01280.0160.01320.01630.01390.01480.01730.0135[SP37]0.0139

is what I want to end up with. What I need to end up with is just columns Value (which should say Date), BMU ID and TSPV but obviously while I'm working it out I'm keeping everything visible. The TSP column uses you pqVLOOKUP function to match the data to the other 3 row table. Ignore the fact that the date and TSP don't actually match because I've falsified this to show you what I want. This is a subset of a 710,000 row dataset.

Regards
Paul
 
Paul, I"m sorry, but I'm still not following.

Is there any way you can load up a small, non-sensitive sample workbook that shows a before and after with real data? Right now I'm still not clear on how these data sets tie together, so I'm not able to really offer any helpful advice. :(
 
File sample

Ken,

The main data is a years worth of energy consumption figures. The figures are baed on half hourly time periods. For most days there are 48 but there will be one day where ther are 46 and one where there are 50, clock cahnge days. From these we select 3 days at random, called Triad days, always in the winter. We then pick half hourly figures for eithe time period 35, 56 or 37. I'm trying to parametise this so that a user can do it themselves. I've got the table of the three days merged with the date for the whole year so I've got tye recordset down to just over 5,000 records. It would be easy if they always pick period 35 and they probably would if they picked 3 days in December and January, but there are rules about how close together the days can be. They also look retrospectively at the weather to attempt to pick the coldest days because energy demand is higher. The last date of the 3 in this year is quite late so daylight time had started to extend so they've opted for period 37 on that day. I can do the whole thing if I write the 'M' code but being a perfectionist I'd like to try and parametise it. File should be attached.

Regards
Paul
 

Attachments

  • TRIAD TEST for Ken.xlsx
    436.7 KB · Views: 13
Okay, so... We need to allow the user to pick from the list of SP35, SP37 or SP56 and filter the SP column based on that choice? Or something else?
 
Ken,

If that's the way we have to do then yes. These are fairly unsophisticated users and the more I can do it for them the better. I just thought if we could hold the SP field number somewhere that would make it easy.

My sql head is thinking down the lines of Select DATE, BMU, SPnum from Tbl where date = Triad_date

and in that I would code variables for the DATE and the SPnum.

Regards
Paul
 
Wait a minute... I think I get it now...

Tell me if I'm right here. You want to allow the users to complete the three values in the Triads table, filter your results to only those records, then extract the value from the appropriate column referenced in the SP column of the Triads table, is that correct? At that point you'll drop all the other SP columns, as they're just being used to calc which value to return. Do I have that correct?

Based on the file you sent through, it looks like you've already got the record filtered to just those from the Triads table, it's just the last part of extracting the correct SP value.

Most of your stuff wouldn't refresh for me, as it's pulling from local file on your system, but what I did was copy paste the results of Merge3 into a new source table, then completed the extraction of the correct TSP value. Tell me if that's the part you were looking to complete or if I missed a step.

(FYI, I had to load the TSP query as connection only to make the workbook small enough to upload.)
 

Attachments

  • TRIAD TEST for Ken.xlsx
    433.6 KB · Views: 20
Ken,

Yes thank you. Now following one ot the 10 commandments of Access development (http://access.mvps.org/access/tencommandments.htm) I'm going to try and understand what it's doing before I copy and paste it into my solution.

Thanks for your help.
Paul
P.S. I've switched my ISP and taken advantage of Office 365. I'd like to say the switch went well but I can't. However, I've resolved most of the issues in between building the model that the above is part of.
 
Ken

Thanks, I've cracked it. The secret is unpivot the data.

I looked at your code and following the principle from Access (http://access.mvps.org/access/tencommandments.htm) that you don't copy and paste code without trying to understand what it's doing, I set about figuring out what you were doing. I've expanded my parameter table so that I've got 'Triad1Date', 'Triad1SP', 'Triad2Date', 'Triad2SP'..... and then I wrote this

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="HHData"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"BMU ID", type text}, {"SP31", type number}, {"SP32", type number}, {"SP33", type number}, {"SP34", type number}, {"SP35", type number}, {"SP36", type number}, {"SP37", type number}, {"SP38", type number}, {"SP39", type number}, {"SP40", type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Date] = #date(Date.Year(fnGetParameter("Triad1Date")), Date.Month(fnGetParameter("Triad1Date")), Date.Day(fnGetParameter("Triad1Date")))),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Date", "BMU ID"}, "Attribute", "Value"),
    #"Filtered Rows1" = Table.SelectRows(#"Unpivoted Columns", each ([Attribute] = ""&fnGetParameter("Triad1SP")&""))
in
    #"Filtered Rows1"

three times changing the Triad variables and then appended the three results together. I feel like the greek philosopher who ran through the streets naked after he'd discovered something. I think it was Archimedes but age and lack of use is dimming my memory on the classics I did at school.

Thanks ever so much for this. I owe you a pint next time we meet.
Paul
 
Perfect, and yes, it's:
  • UnPivot
  • Test for column equality
  • Filter out the non-matches

Good policy on code understanding and glad it's working. :)
 
You must log in or register to reply here.
Back
Top