Hi, I'm relatively new to PowerBI and PowerQuery, so apologies if the terminology below is incorrect.I'm trying to do something that I feel 'should' be easy; I have data set out as follows; IDTitleLink ID123Apple125125Banana-127Orange- I want to create a new column that reads the the link ID, finds the relevent row with that ID and then inputs the title of that row. So based on the data above, i'd like to end up with this;IDTitleLink IDLink Title123Apple125Banana125Banana- 127Orange- Any idea on how to do this? Thanks

Hello ChrisBroome I included now some errorhandling let Source = Csv.Document(File.Contents("C:\Users\Chris.Broome\Downloads\TISReport_15-20201023_130241.csv"),[Delimiter=",", Columns=50, Encoding=65001, QuoteStyle=QuoteStyle.Csv]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}, {"Column23", type text}, {"Column24", type text}, {"Column25", type text}, {"Column26", type text}, {"Column27", type text}, {"Column28", type text}, {"Column29", type text}, {"Column30", type text}, {"Column31", type text}, {"Column32", type text}, {"Column33", type text}, {"Column34", type text}, {"Column35", type text}, {"Column36", type text}, {"Column37", type text}, {"Column38", type text}, {"Column39", type text}, {"Column40", type text}, {"Column41", type text}, {"Column42", type text}, {"Column43", type text}, {"Column44", type text}, {"Column45", type text}, {"Column46", type text}, {"Column47", type text}, {"Column48", type text}, {"Column49", type text}, {"Column50", type text}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "ID"}, {"Column2", "Title"}, {"Column3", "Link ID"}}), #"PreviousStep" = Table.TransformColumnTypes(#"Renamed Columns",{{"ID", Int64.Type}, {"Title", type text}, {"Link ID", Int64.Type}}), AddColumn = Table.AddColumn ( PreviousStep, "Link Title", each try Table.SelectRows(PreviousStep, (sel)=> sel[ID]=_[Link ID])[Title]{0} otherwise null ) in AddColumn Copy paste this code to the advanced editor in a new blank query to see how the solution works.If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)Kudoes are nice tooHave funJimmy

How to reference and copy data from one row to another

9 Replies

AlB
Community Champion
5 years ago
Hi ChrisBroome

You can do that in DAX or PQ. For the latter, place the following M code in a blank query to see the steps:

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRciwoyEkF0oZGpkqxOtFgWkfJKTEPCIEMqJg5kOlflJiXngoWiwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Title = _t, #"Link ID" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Title", type text}, {"Link ID", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each try #"Changed Type"[Title]{List.PositionOf(#"Changed Type"[ID], [Link ID])} otherwise null) in #"Added Custom"

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers
- ChrisBroome
  Frequent Visitor
  5 years ago
  Hi AlB,
  
  thanks for the help.
  
  I get a 'token Eof expected' error when entering this information. I've tried to add a custom column and entered this as the formula. is that correct? it highlights the #changed type as being the error (in red below);
  let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRciwoyEkF0oZGpkqxOtFgWkfJKTEPCIEMqJg5kOlflJiXngoWiwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Title = _t, #"Link ID" = _t]),
  #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Title", type text}, {"Link ID", Int64.Type}}),
  #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each try #"Changed Type"[Title]{List.PositionOf(#"Changed Type"[ID], [Link ID])} otherwise null)
  in
  #"Added Custom"
  - AlB
    Community Champion
    5 years ago
    ChrisBroome
    
    I don't get any error. Have a look at the attached file.
    
    Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
    
    Contact me privately for support with any larger-scale BI needs, tutoring, etc.
    
    Cheers
    
    How to reference and copy data from one row to another.pbix14 KB

CNENFRNL

Community Champion

5 years ago

One more solution, have fun

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRciwoyEkF0oZGpkqxOtFgWkfJKTEPCIEMqJg5kOlflJiXngoWiwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Title = _t, #"Link ID" = _t]),

    #"Added Custom" = Table.AddColumn(Source, "Custom", each try Record.Field(Source{[ID=[Link ID]]}, "Title") otherwise null)
in
    #"Added Custom"

Jimmy801
Community Champion
5 years ago
Hello ChrisBroome

answering the same time 🙂 . -however this is a little bit of another approach.

Add a new column with this forumla

Table.SelectRows(PreviousStep, (sel)=> sel[ID]=_[Link ID])[Title]{0}

Here a complete example

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRciwoyEkF0oZGpkqxOtFgWkfJKTEPCMHCxlBhcyDPvygxLx2i2lwpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Title = _t, #"Link ID" = _t]), PreviousStep = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Title", type text}, {"Link ID", Int64.Type}}), AddColumn = Table.AddColumn ( PreviousStep, "Link Title", each Table.SelectRows(PreviousStep, (sel)=> sel[ID]=_[Link ID])[Title]{0} ) in AddColumn

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy
- ChrisBroome
  Frequent Visitor
  5 years ago
  Jimmy801
  
  I've managed to get the advanced editor looking like this;
  let
  Source = Csv.Document(File.Contents("C:\Users\Chris.Broome\Downloads\TISReport_15-20201023_130241.csv"),[Delimiter=",", Columns=50, Encoding=65001, QuoteStyle=QuoteStyle.Csv]),
  #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}, {"Column23", type text}, {"Column24", type text}, {"Column25", type text}, {"Column26", type text}, {"Column27", type text}, {"Column28", type text}, {"Column29", type text}, {"Column30", type text}, {"Column31", type text}, {"Column32", type text}, {"Column33", type text}, {"Column34", type text}, {"Column35", type text}, {"Column36", type text}, {"Column37", type text}, {"Column38", type text}, {"Column39", type text}, {"Column40", type text}, {"Column41", type text}, {"Column42", type text}, {"Column43", type text}, {"Column44", type text}, {"Column45", type text}, {"Column46", type text}, {"Column47", type text}, {"Column48", type text}, {"Column49", type text}, {"Column50", type text}}),
  #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "ID"}, {"Column2", "Title"}, {"Column3", "Link ID"}}),
  #"PreviousStep" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Title", type text}, {"Link ID", Int64.Type}}),
  AddColumn = Table.AddColumn
  (
  PreviousStep,
  "Link Title",
  each Table.SelectRows(PreviousStep, (sel)=> sel[ID]=_[Link ID])[Title]{0}
  )
  in
  AddColumn
  
  But get this following error;
  Expression.Error: The column 'ID' of the table wasn't found.
  Details:
  ID
  
  Any thoughts on why?
  - Jimmy801
    Community Champion
    5 years ago
    Hello ChrisBroome
    
    the connection between the steps was not ok. Check out this. If you have performance issue let me know
    
    let Source = Csv.Document(File.Contents("C:\Users\Chris.Broome\Downloads\TISReport_15-20201023_130241.csv"),[Delimiter=",", Columns=50, Encoding=65001, QuoteStyle=QuoteStyle.Csv]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}, {"Column23", type text}, {"Column24", type text}, {"Column25", type text}, {"Column26", type text}, {"Column27", type text}, {"Column28", type text}, {"Column29", type text}, {"Column30", type text}, {"Column31", type text}, {"Column32", type text}, {"Column33", type text}, {"Column34", type text}, {"Column35", type text}, {"Column36", type text}, {"Column37", type text}, {"Column38", type text}, {"Column39", type text}, {"Column40", type text}, {"Column41", type text}, {"Column42", type text}, {"Column43", type text}, {"Column44", type text}, {"Column45", type text}, {"Column46", type text}, {"Column47", type text}, {"Column48", type text}, {"Column49", type text}, {"Column50", type text}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "ID"}, {"Column2", "Title"}, {"Column3", "Link ID"}}), #"PreviousStep" = Table.TransformColumnTypes(#"Renamed Columns",{{"ID", Int64.Type}, {"Title", type text}, {"Link ID", Int64.Type}}), AddColumn = Table.AddColumn ( PreviousStep, "Link Title", each Table.SelectRows(PreviousStep, (sel)=> sel[ID]=_[Link ID])[Title]{0} ) in AddColumn
    
    Copy paste this code to the advanced editor in a new blank query to see how the solution works.
    
    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too
    
    Have fun
    
    Jimmy

Forum Discussion

How to reference and copy data from one row to another

9 Replies

Related content

Copy entire Lakehouse to another Workspace

Reference one notebook from another

Copy Job vs Copy Activity

Reference a measure in another measure

Copy paths

ID	Title	Link ID	Link Title
123	Apple	125	Banana
125	Banana	-
127	Orange	-