PowerQuery Copy paste a value between rows

[Cairn73]

Hello community,

I'm looking for the way to copy a value from a row to another with some criteria.

I want to do this on PowerQuery :

See below an example of my tab

Mytable source :

EndDate	Service	type	Charge Label	IdResource	Amount HT	ProjectName	Qty	State-Cost
20210228	VM	xlarge.4		0065a356-c566-4d0a-a9aa-ca8b7860a023	HISTO	test1	1	HS
20210228	VM	xlarge.4		0065a356-c566-4d0a-a9aa-ca8b7860a023	HISTO	test1	2	HS
20210228	VM	xlarge.4		0065a356-c566-4d0a-a9aa-ca8b7860a023	HISTO	test1	3	HS
20210228	VM	xlarge.4		0065a356-c566-4d0a-a9aa-ca8b7860a023	HISTO	test1	4	HS
20210228	VM	xlarge.4		0065a356-c566-4d0a-a9aa-ca8b7860a023	HISTO	test1	5	HS
20210228	VM	xlarge.4		0065a356-c566-4d0a-a9aa-ca8b7860a023	HISTO	test1	6	HS
20210228	VM	xlarge.4		0065a356-c566-4d0a-a9aa-ca8b7860a023	HISTO	test1	7	HS
20210228	VM	xlarge.4		0065a356-c566-4d0a-a9aa-ca8b7860a023	HISTO	test1	8	HS
20210228	VM	xlarge.4		0065a356-c566-4d0a-a9aa-ca8b7860a023	HISTO	test1	9	HS
20210228	VM	xlarge.4		0065a356-c566-4d0a-a9aa-ca8b7860a023	HISTO	test1	10	HS
20210228	VM	xlarge.4		0065a356-c566-4d0a-a9aa-ca8b7860a023	HISTO	test1	11	HS
20210228	VM	xlarge.4	xlarge.4	0065a356-c566-4d0a-a9aa-ca8b7860a023	RESERVED	66	RSD
20210226	VM	large.2		12567816-2148-4139-8126-443fd3550cd4	HISTO	test2	1	HS
20210227	VM	large.2		12567816-2148-4139-8126-443fd3550cd4	HISTO	test2	1	HS
20210228	VM	large.2		12567816-2148-4139-8126-443fd3550cd4	HISTO	test3	1	HS
20210228	VM	large.2	Large.2	12567816-2148-4139-8126-443fd3550cd4	RESERVED	1	RSD

Wanted Results :

EndDate	Service	type	Charge Label	IdResource	Amount HT	ProjectName	Qty	State-Cost
20210228	VM	xlarge.4		0065a356-c566-4d0a-a9aa-ca8b7860a023	HISTO	test1	1	HS
20210228	VM	xlarge.4		0065a356-c566-4d0a-a9aa-ca8b7860a023	HISTO	test1	2	HS
20210228	VM	xlarge.4		0065a356-c566-4d0a-a9aa-ca8b7860a023	HISTO	test1	3	HS
20210228	VM	xlarge.4		0065a356-c566-4d0a-a9aa-ca8b7860a023	HISTO	test1	4	HS
20210228	VM	xlarge.4		0065a356-c566-4d0a-a9aa-ca8b7860a023	HISTO	test1	5	HS
20210228	VM	xlarge.4		0065a356-c566-4d0a-a9aa-ca8b7860a023	HISTO	test1	6	HS
20210228	VM	xlarge.4		0065a356-c566-4d0a-a9aa-ca8b7860a023	HISTO	test1	7	HS
20210228	VM	xlarge.4		0065a356-c566-4d0a-a9aa-ca8b7860a023	HISTO	test1	8	HS
20210228	VM	xlarge.4		0065a356-c566-4d0a-a9aa-ca8b7860a023	HISTO	test1	9	HS
20210228	VM	xlarge.4		0065a356-c566-4d0a-a9aa-ca8b7860a023	HISTO	test1	10	HS
20210228	VM	xlarge.4		0065a356-c566-4d0a-a9aa-ca8b7860a023	HISTO	test1	11	HS
20210228	VM	xlarge.4	xlarge.4	0065a356-c566-4d0a-a9aa-ca8b7860a023	RESERVED	test1	66	RSD
20210226	VM	large.2		12567816-2148-4139-8126-443fd3550cd4	HISTO	test2	1	HS
20210227	VM	large.2		12567816-2148-4139-8126-443fd3550cd4	HISTO	test2	1	HS
20210228	VM	large.2		12567816-2148-4139-8126-443fd3550cd4	HISTO	test3	1	HS
20210228	VM	large.2	Large.2	12567816-2148-4139-8126-443fd3550cd4	RESERVED	test3	1	RSD

Thx by advance for your help.

Regards

Cairn

[Anonymous · Accepted solution]

this should correspond to your last example, although I still have some doubts about the general validity of your situations. but in case you will provide some more exhaustive examples

[Anonymous]

the second script should perform better also for lots of data

[Cairn73]

Hello,

Firstly thank you for you answer and sorry for my reply delay.

I tried your solution but there are some issues with it:

- Your script seems to use only the lines present in the table up there (which is a simplified version of what I really have) so when I want to adapt it, There is multiple IdResource line blank and the same goes for the other project line that are not concerned with my request.

- Some of the project names do not correspond to their IDResource To simplify the request what I want is:

if [mode] = "Usage" & [type] = R then [Project] = First(table, [Project] where table[RessourceId] = [RessourceId] and [Status]= "H")

mode Idresource project tags type
usage ressource 1 P1 T1 H
usage ressource 1 P1 T1 H
usage ressource 2 P2 T2 H
usage ressource 2 P2 T2 H
usage ressource 1

R
usage ressource 2

R
sub ressource 3 P3 T3 R
sub ressource 4 P4 T4 R
usage ressource 5 P5 T5 H

[Cairn73]

hello, 

Sorry if i was not clear, I just want to complete empty field base on the previews rows

So i want to complete all line with Type is "R" with empty projet & tags columns.

And i want to use information providing previous row with the same Idresource.

I want to post a pbix file but it failed 😞 so i put you a screenshot of an example

Initial State :

Results : 

Regards

[Anonymous · Accepted solution]

an alternative way

[Anonymous · Accepted solution]

this should correspond to your last example, although I still have some doubts about the general validity of your situations. but in case you will provide some more exhaustive examples

[Anonymous]

I'm sorry, but I can't figure out which tables you are referring to. If you can, upload tables to an excel sheet on a file sharing website. One is the starting point (with all the cases of interest: I cannot invent situations that I do not know); a second with the desired result. A clear and complete explanation of the transformation logic would be helpful.

[Vera_33]

Hi @Cairn73 

If you sorted your table, then retrieve the one above? Performace may be not good if you have lots of data

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZTBCsIwDIbfZedVkrTNuruDCYqwyS5jh7pNL550Bx/flqEM8bCDgab5D+X7CUnatgkBIRC5JE2aQ7ieN3+/jhsTZDgAbL22rHrLrMwAXvnce9V7d84cgwfS4Vm5q0/HkKfxMWHIMco66VIhPgnztTDfCPOtMJ+F+Zkw3wnzc2E+grTBug1eyJVGVVEXVVNs4xDFKarqKJc+/PaZ2TTXgWQ5c8iK0DhlUOfKIQUjoy+Dthb6wXzVQT8/okwW7/6F12vx+49a6bJoAS460L0A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [EndDate = _t, Service = _t, #"type" = _t, #"Charge Label" = _t, IdResource = _t, #"Amount HT" = _t, ProjectName = _t, Qty = _t, #"State-Cost" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"EndDate", Int64.Type}, {"Service", type text}, {"type", type text}, {"Charge Label", type text}, {"IdResource", type text}, {"Amount HT", type text}, {"ProjectName", type text}, {"Qty", type text}, {"State-Cost", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "EndDate", "Service", "type", "Charge Label", "IdResource", "Amount HT", "ProjectName", "Qty", "State-Cost"}),
    #"Added Custom" = Table.AddColumn(#"Reordered Columns", "Custom", each [a =Record.ToList(_),
b= if [Qty] = "RSD" then List.RemoveLastN( List.InsertRange(a, 6,{#"Reordered Columns"{[Index]-1}[ProjectName]}),1) else a,
c=List.RemoveFirstN(b,1)][c]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom1", each Record.FromList([Custom],Table.ColumnNames( Source))),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Custom1"}),
    #"Expanded Custom1" = Table.ExpandRecordColumn(#"Removed Other Columns", "Custom1", {"EndDate", "Service", "type", "Charge Label", "IdResource", "Amount HT", "ProjectName", "Qty", "State-Cost"}, {"EndDate", "Service", "type", "Charge Label", "IdResource", "Amount HT", "ProjectName", "Qty", "State-Cost"})
in
    #"Expanded Custom1"