converting a excel formula to work in PowerQuery

[Lyndonu]

I'm new to PowerQuery, so what I'm attempting is way over my head.

In the following table, I've identified the earliest duplicate JobID value (by sorting the timestamp row earliest to latest), and then have populated the identified earliest Estimate Values into column E. (Ultimately I need to know the value of the first estimate uploaded for each JobID so I can track the changes made later.)

formulas for D2 is =(COUNTIF($C$2:C2,C2)>=2)+0
for E2 is =IF(D2=0,B2,"")

How could I accomplish creating the Initial Estimate Value column with powerquery? or is it even possible?

 

[alansidman]

Cannot manipulate data in a picture. Please reload the data to this site using XL2BB so we don't have to recreate the data you already have.

I will attempt to walk you through this without using the data, however.

With your data in the PQ editor. Highlight the Job ID and select Group By. New column Name is Earliest, and you are selecting the Time Stamp Column and selecting Min.
This will give you the earliest estimate for each Job ID.

 

[Lyndonu]

Book2
	A	B	C	D	E
1	TimeStamp	Estimate Value	JobID	Estimate Order	Initial Estimate Value
2	17/10/2018 12:41	$9,000.00	RB1111	0	$9,000.00
3	27/11/2018 12:07	$13,500.00	FL42124	0	$13,500.00
4	03/12/2018 13:34	$12,000.00	RB1347	0	$12,000.00
5	03/12/2018 17:46	$3,000.00	FL11598	0	$3,000.00
6	06/12/2018 12:28	$6,350.00	RB1313	0	$6,350.00
7	12/12/2018 13:38	$5,137.76	RB1313	1
8	12/12/2018 13:38	$2,897.91	RB2100	0	$2,897.91
9	21/12/2018 12:27	$3,701.80	FL11598	1
10	31/12/2018 17:08	$15,747.94	RB111	0	$15,747.94
11	02/01/2019 9:58	$20,587.02	FL42124	1
12	02/01/2019 11:57	$39,580.35	FL11400	0	$39,580.35
13	02/01/2019 13:52	$5,591.57	SC101	0	$5,591.57
14	02/01/2019 14:07	$8,415.25	SC101	1
Sheet1

Cell Formulas
Range	Formula
D2:D14	D2	=(COUNTIFS($C$2:C2,C2)>=2)+0
E2:E14	E2	=IF(D2=0,B2,"")

 

[Lyndonu]

this isnt quite what I'm looking for.

I'd like to add a new column that shows the Initial Estimate Value column in line with the rest of the data as listed above in the table. The grouping feature removes columns, however I need to reference them for other calculations.

Columns A to C are provided to me. I need to add calculated column E to assist with other calculated columns not showing here (F to L). Column D is unnecessary as it was a helper column in Excel only. I'm really trying to automate this worksheet build so it doesnt need my ongoing input.

 

[JGordon11]

Must be better ways than this, but try:

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    ChangeType = Table.TransformColumnTypes(Source,{{"TimeStamp", type datetime}, {"Estimate Value", type number}, {"JobID", type text}}),
    lst1 = Table.AddColumn(ChangeType, "OriginalEstimate", (x)=> 
        Table.Sort(Table.SelectRows(ChangeType, each (x[JobID] = _[JobID])),{{"TimeStamp", Order.Ascending}}))[OriginalEstimate],
    lst2 = List.Transform(lst1, each _[Estimate Value]{0}),
    Result = Table.FromColumns(Table.ToColumns(ChangeType) & {lst2}, Table.ColumnNames(ChangeType) & {"Original Estimate"})
in
    Result

delme1R1.xlsm
	A	B	C	D	E	F	G	H	I
1	TimeStamp	Estimate Value	JobID		TimeStamp	Estimate Value	JobID	Original Estimate
2	10/17/18 12:41	9000	RB1111		10/17/2018 12:41	9000	RB1111	9000
3	11/27/18 12:07	13500	FL42124		11/27/2018 12:07	13500	FL42124	13500
4	12/3/18 13:34	12000	RB1347		12/3/2018 13:34	12000	RB1347	12000
5	12/3/18 17:46	3000	FL11598		12/3/2018 17:46	3000	FL11598	3000
6	12/6/18 12:28	6350	RB1313		12/6/2018 12:28	6350	RB1313	6350
7	12/12/18 13:38	5137.76	RB1313		12/12/2018 13:38	5137.76	RB1313	6350
8	12/12/18 13:38	2897.91	RB2100		12/12/2018 13:38	2897.91	RB2100	2897.91
9	12/21/18 12:27	3701.8	FL11598		12/21/2018 12:27	3701.8	FL11598	3000
10	12/31/18 17:08	15747.94	RB111		12/31/2018 17:08	15747.94	RB111	15747.94
11	1/2/19 9:58	20587.02	FL42124		1/2/2019 9:58	20587.02	FL42124	13500
12	1/2/19 11:57	39580.35	FL11400		1/2/2019 11:57	39580.35	FL11400	39580.35
13	1/2/19 13:52	5591.57	SC101		1/2/2019 13:52	5591.57	SC101	5591.57
14	1/2/19 14:07	8415.25	SC101		1/2/2019 14:07	8415.25	SC101	5591.57
15
Sheet7