How to reference and copy data from one row to another

[ChrisBroome]

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;

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

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;

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

Any idea on how to do this?

Thanks

[CNENFRNL]

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]

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]

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

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

[ChrisBroome]

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.

[Jimmy801 · 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

[AlB]

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]

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]

@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