cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Cairn73
Frequent Visitor

PowerQuery Copy paste a value between rows

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 :

EndDateServicetypeCharge LabelIdResourceAmount HTProjectNameQtyState-Cost
20210228VMxlarge.4 0065a356-c566-4d0a-a9aa-ca8b7860a023HISTOtest11HS
20210228VMxlarge.4 0065a356-c566-4d0a-a9aa-ca8b7860a023HISTOtest12HS
20210228VMxlarge.4 0065a356-c566-4d0a-a9aa-ca8b7860a023HISTOtest13HS
20210228VMxlarge.4 0065a356-c566-4d0a-a9aa-ca8b7860a023HISTOtest14HS
20210228VMxlarge.4 0065a356-c566-4d0a-a9aa-ca8b7860a023HISTOtest15HS
20210228VMxlarge.4 0065a356-c566-4d0a-a9aa-ca8b7860a023HISTOtest16HS
20210228VMxlarge.4 0065a356-c566-4d0a-a9aa-ca8b7860a023HISTOtest17HS
20210228VMxlarge.4 0065a356-c566-4d0a-a9aa-ca8b7860a023HISTOtest18HS
20210228VMxlarge.4 0065a356-c566-4d0a-a9aa-ca8b7860a023HISTOtest19HS
20210228VMxlarge.4 0065a356-c566-4d0a-a9aa-ca8b7860a023HISTOtest110HS
20210228VMxlarge.4 0065a356-c566-4d0a-a9aa-ca8b7860a023HISTOtest111HS
20210228VMxlarge.4xlarge.40065a356-c566-4d0a-a9aa-ca8b7860a023RESERVED66RSD
20210226VMlarge.2 12567816-2148-4139-8126-443fd3550cd4HISTOtest21HS
20210227VMlarge.2 12567816-2148-4139-8126-443fd3550cd4HISTOtest21HS
20210228VMlarge.2 12567816-2148-4139-8126-443fd3550cd4HISTOtest31HS
20210228VMlarge.2Large.212567816-2148-4139-8126-443fd3550cd4RESERVED1RSD

 

Wanted Results :

EndDateServicetypeCharge LabelIdResourceAmount HTProjectNameQtyState-Cost
20210228VMxlarge.4 0065a356-c566-4d0a-a9aa-ca8b7860a023HISTOtest11HS
20210228VMxlarge.4 0065a356-c566-4d0a-a9aa-ca8b7860a023HISTOtest12HS
20210228VMxlarge.4 0065a356-c566-4d0a-a9aa-ca8b7860a023HISTOtest13HS
20210228VMxlarge.4 0065a356-c566-4d0a-a9aa-ca8b7860a023HISTOtest14HS
20210228VMxlarge.4 0065a356-c566-4d0a-a9aa-ca8b7860a023HISTOtest15HS
20210228VMxlarge.4 0065a356-c566-4d0a-a9aa-ca8b7860a023HISTOtest16HS
20210228VMxlarge.4 0065a356-c566-4d0a-a9aa-ca8b7860a023HISTOtest17HS
20210228VMxlarge.4 0065a356-c566-4d0a-a9aa-ca8b7860a023HISTOtest18HS
20210228VMxlarge.4 0065a356-c566-4d0a-a9aa-ca8b7860a023HISTOtest19HS
20210228VMxlarge.4 0065a356-c566-4d0a-a9aa-ca8b7860a023HISTOtest110HS
20210228VMxlarge.4 0065a356-c566-4d0a-a9aa-ca8b7860a023HISTOtest111HS
20210228VMxlarge.4xlarge.40065a356-c566-4d0a-a9aa-ca8b7860a023RESERVEDtest166RSD
20210226VMlarge.2 12567816-2148-4139-8126-443fd3550cd4HISTOtest21HS
20210227VMlarge.2 12567816-2148-4139-8126-443fd3550cd4HISTOtest21HS
20210228VMlarge.2 12567816-2148-4139-8126-443fd3550cd4HISTOtest31HS
20210228VMlarge.2Large.212567816-2148-4139-8126-443fd3550cd4RESERVEDtest31RSD

 

Thx by advance for your help.

 

Regards

Cairn

2 ACCEPTED SOLUTIONS

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

View solution in original post

an alternative way

View solution in original post

7 REPLIES 7
Rocco_sprmnt21
Super User II
Super User II

the second script should perform better also for lots of data

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
Frequent Visitor

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 :

Cairn73_0-1617796660702.png

 

Results : 

Cairn73_1-1617796694714.png

 

Regards

an alternative way

View solution in original post

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

View solution in original post

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
Solution Sage
Solution Sage

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"

Vera_33_0-1617354298144.png

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors