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

Accepted Solutions

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
Super User II
Super User II

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

Super User II
Super User II

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

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

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

Resident Rockstar
Resident Rockstar

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"

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

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors