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

How to reference and copy data from one row to another

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 ID
123Apple125
125Banana-
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 Title
123Apple125Banana
125Banana- 
127Orange-

 

 

 

Any idea on how to do this?

 

Thanks

1 ACCEPTED SOLUTION

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 too

Have fun

Jimmy

View solution in original post

9 REPLIES 9
Community Champion
Community Champion

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"
Super User III
Super User III

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

@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?

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

Hi @Jimmy801 

 

Thanks for your suggestions. I think i'm nearly there; i'm able to get the references working correctly, but any row that doesn't have a reference is now showing an error message;

 

Expression.Error: There weren't enough elements in the enumeration to complete the operation.
Details:
[List]

 

I thought the issue may be solved by adding an 'otherwise Null' command, but can't get this to show in the following code without getting a syntax error;

 

let
Source = Csv.Document(File.Contents("C:\Users\Chris.Broome\Downloads\TISReport_15-20201023_130241.csv"),[Delimiter=",", Columns=50, Encoding=65001, QuoteStyle=QuoteStyle.Csv]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
AddColumn = Table.AddColumn
(
#"Promoted Headers",
"Link Title",
each Table.SelectRows(#"Promoted Headers", (sel)=> sel[Key]=_[Epic Link])[Summary]{0}),
#"Reordered Columns" = Table.ReorderColumns(AddColumn,{"Key", "Summary", "Epic Link", "Link Title", "Team (WEB)", "Fix Version/s", "Issue Type", "Open", "Done", "Backlog", "In Test", "Ready For Test", "SignOff", "Code Review", "In Development", "Ready for Development", "New", "Under review", "Obsolete", "Out of Scope", "In Build", "Ready for Dev", "In Design", "Scoping", "Scoping in Progress", "Development in progress", "Ready for code review", "Code Review in progress", "Ready for QA", "QA in Progress", "New Idea", "Dev In progress", "Ready for UAT", "Dev Done - Not Deployed", "Refining", "Reviewed", "Ready To Test", "Design - WIP", "Ready for Review", "Rework In Progress", "Approved by Business", "Refining Requirements", "Approved Dev Ready", "Task Complete", "Requires sizing", "Amigos Review", "Open/Reopened", "Sign Off", "In Progress", "Resolved", "Closed"}),
#"Link Title" = #"Reordered Columns"{3}[Link Title]
in
#"Link Title"

 

So two questions;

1. how do I resolve the errors for rows with no reference, and

2. if turning them into null values will resolve this, how can i modify the above code to include this?

 

Thanks in Advance.

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 too

Have fun

Jimmy

View solution in original post

Super User III
Super User III

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 

SU18_powerbi_badge

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"

 

@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 

SU18_powerbi_badge

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors