Fill in value if unique key match

can you help me with the pbix file

Hello Anonymous;,

In my solution you have to replace the Source step with your data.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci3ITFbSUfJ0843UNTIyAzJ9ffwLihUCivKzUpNLkKVidaKVgkvyiyoRguZAJroSVBMtgEwfH1+gkchimOos4Tan5Rcp+AWEIkthKDc2gBirG+Tojm4uqhONDZGdCNSHRYkRii8g1oUkFmcjVBhjMQTVQSZAZnhmWqZCcGpecWZeuoJjcklmWWZJpUJQanJ+el5mSWZ+HrJyTEtMUSzBpsIMPbhjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Issue Type" = _t, issues.key = _t, #"Project Name" = _t, issues.fields.customfield_11900 = _t]),
    Type = Table.TransformColumnTypes(Source,{{"Issue Type", type text}, {"issues.key", type text}, {"Project Name", type text}, {"issues.fields.customfield_11900", type text}}),
    Columns = Table.SelectColumns(Type,{"issues.key", "Project Name"}),
    Rows = Table.SelectRows(Columns, each ([Project Name] <> "")),
    Merged = Table.NestedJoin(Type, {"issues.fields.customfield_11900"}, Rows, {"issues.key"}, "Move Columns", JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Merged, "Move Columns", {"Project Name"}, {"Project Name Full"})
in
    Expand

Best regards from Germany
Manuel Bolz

If this post helped you, please consider Accept as Solution so other members can find it faster.

🤝Follow me on LinkedIn

let
    Source = your_table,
    filled = Table.SelectRows(Source, each [Project Name] <> null),
    prj = Record.FromList(
        filled[Project Name],
        filled[issues.key]
    ),
    result = Table.ReplaceValue(
        Source, 
        null, 
        (x) => x[issues.fields.customfield_11900],
        (v, o, n) => v ?? Record.FieldOrDefault(prj, n),
        {"Project Name"}
    )
in
    result

Hi, thank you for the advice.

Here is my current data source file. I did use Group By option to group rows based on issues.key and combine multiple text value in row for Component Column.
Can I know where should I start to edit?

let
    Source = Json.Document(Web.Contents("https://XXXX/XXXX/XXXX")),
    #"Converted to Table" = Table.FromRecords({Source}),
    #"Expanded issues" = Table.ExpandListColumn(#"Converted to Table", "issues"),
    #"Expanded issues1" = Table.ExpandRecordColumn(#"Expanded issues", "issues", {"expand", "id", "self", "key", "fields"}, {"issues.expand", "issues.id", "issues.self", "issues.key", "issues.fields"}),
    #"Expanded issues.fields" = Table.ExpandRecordColumn(#"Expanded issues1", "issues.fields", {"summary", "issuetype", "components", "created", "fixVersions", "priority", "customfield_34216", "labels", "customfield_11900", "customfield_35401", "customfield_11901", "duedate", "resolutiondate", "assignee", "updated", "status"}, {"issues.fields.summary", "issues.fields.issuetype", "issues.fields.components", "issues.fields.created", "issues.fields.fixVersions", "issues.fields.priority", "issues.fields.customfield_34216", "issues.fields.labels", "issues.fields.customfield_11900", "issues.fields.customfield_35401", "issues.fields.customfield_11901", "issues.fields.duedate", "issues.fields.resolutiondate", "issues.fields.assignee", "issues.fields.updated", "issues.fields.status"}),
    #"Expanded issues.fields.customfield_35401" = Table.ExpandListColumn(#"Expanded issues.fields", "issues.fields.customfield_35401"),
    #"Expanded issues.fields.customfield_1" = Table.ExpandRecordColumn(#"Expanded issues.fields.customfield_35401", "issues.fields.customfield_35401", {"displayName"}, {"issues.fields.customfield_35401.displayName"}),
    Sponsor = Table.RenameColumns(#"Expanded issues.fields.customfield_1",{{"issues.fields.customfield_35401.displayName", "issues.fields.customfield_35401"}}),
    #"Expanded issues.fields.assignee" = Table.ExpandRecordColumn(Sponsor, "issues.fields.assignee", {"displayName"}, {"issues.fields.assignee.displayName"}),
    Assignee = Table.RenameColumns(#"Expanded issues.fields.assignee",{{"issues.fields.assignee.displayName", "issues.fields.assignee"}}),
    #"Expanded issues.fields.fixVersions" = Table.ExpandListColumn(Assignee, "issues.fields.fixVersions"),
    #"Expanded issues.fields.fixVersions1" = Table.ExpandRecordColumn(#"Expanded issues.fields.fixVersions", "issues.fields.fixVersions", {"name"}, {"issues.fields.fixVersions.name"}),
    Version = Table.RenameColumns(#"Expanded issues.fields.fixVersions1",{{"issues.fields.fixVersions.name", "issues.fields.fixVersions"}}),
    #"Expanded issues.fields.components" = Table.ExpandListColumn(Version, "issues.fields.components"),
    #"Expanded issues.fields.components1" = Table.ExpandRecordColumn(#"Expanded issues.fields.components", "issues.fields.components", {"name"}, {"issues.fields.components.name"}),
    Components = Table.RenameColumns(#"Expanded issues.fields.components1",{{"issues.fields.components.name", "issues.fields.components"}}),
    #"Expanded issues.fields.labels" = Table.ExpandListColumn(Components, "issues.fields.labels"),
    #"Expanded issues.fields.issuetype" = Table.ExpandRecordColumn(#"Expanded issues.fields.labels", "issues.fields.issuetype", {"self", "id", "description", "iconUrl", "name", "subtask", "avatarId"}, {"issues.fields.issuetype.self", "issues.fields.issuetype.id", "issues.fields.issuetype.description", "issues.fields.issuetype.iconUrl", "issues.fields.issuetype.name", "issues.fields.issuetype.subtask", "issues.fields.issuetype.avatarId"}),
    #"Expanded issues.fields.priority" = Table.ExpandRecordColumn(#"Expanded issues.fields.issuetype", "issues.fields.priority", {"self", "iconUrl", "name", "id"}, {"issues.fields.priority.self", "issues.fields.priority.iconUrl", "issues.fields.priority.name", "issues.fields.priority.id"}),
    #"Expanded issues.fields.status" = Table.ExpandRecordColumn(#"Expanded issues.fields.priority", "issues.fields.status", {"self", "description", "iconUrl", "name", "id", "statusCategory"}, {"issues.fields.status.self", "issues.fields.status.description", "issues.fields.status.iconUrl", "issues.fields.status.name", "issues.fields.status.id", "issues.fields.status.statusCategory"}),
    #"Expanded issues.fields.status.statusCategory" = Table.ExpandRecordColumn(#"Expanded issues.fields.status", "issues.fields.status.statusCategory", {"self", "id", "key", "colorName", "name"}, {"issues.fields.status.statusCategory.self", "issues.fields.status.statusCategory.id", "issues.fields.status.statusCategory.key", "issues.fields.status.statusCategory.colorName", "issues.fields.status.statusCategory.name"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded issues.fields.status.statusCategory",{{"expand", type text}, {"startAt", Int64.Type}, {"maxResults", Int64.Type}, {"total", Int64.Type}, {"issues.expand", type text}, {"issues.id", Int64.Type}, {"issues.self", type text}, {"issues.key", type text}, {"issues.fields.summary", type text}, {"issues.fields.issuetype.self", type text}, {"issues.fields.issuetype.id", Int64.Type}, {"issues.fields.issuetype.description", type text}, {"issues.fields.issuetype.iconUrl", type text}, {"issues.fields.issuetype.name", type text}, {"issues.fields.issuetype.subtask", type logical}, {"issues.fields.issuetype.avatarId", Int64.Type}, {"issues.fields.components", type any}, {"issues.fields.created", type datetimezone}, {"issues.fields.fixVersions", type any}, {"issues.fields.priority.self", type text}, {"issues.fields.priority.iconUrl", type text}, {"issues.fields.priority.name", type text}, {"issues.fields.priority.id", Int64.Type}, {"issues.fields.customfield_34216", Int64.Type}, {"issues.fields.labels", type any}, {"issues.fields.customfield_11900", type text}, {"issues.fields.customfield_35401", type any}, {"issues.fields.customfield_11901", type text}, {"issues.fields.duedate", type date}, {"issues.fields.resolutiondate", type datetimezone}, {"issues.fields.assignee", type any}, {"issues.fields.updated", type datetimezone}, {"issues.fields.status.self", type text}, {"issues.fields.status.description", type text}, {"issues.fields.status.iconUrl", type text}, {"issues.fields.status.name", type text}, {"issues.fields.status.id", Int64.Type}, {"issues.fields.status.statusCategory.self", type text}, {"issues.fields.status.statusCategory.id", Int64.Type}, {"issues.fields.status.statusCategory.key", type text}, {"issues.fields.status.statusCategory.colorName", type text}, {"issues.fields.status.statusCategory.name", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"issues.key"}, {{"Project Name", each List.Max([issues.fields.customfield_11901]), type nullable text}, {"Summary", each List.Max([issues.fields.summary]), type nullable text}, {"Priority", each List.Max([issues.fields.priority.name]), type nullable text}, {"Sponsor", each List.Max([issues.fields.customfield_35401]), type nullable list}, {"Assignee", each List.Max([issues.fields.assignee]), type nullable text}, {"Components", each Text.Combine([issues.fields.components], ";"), type nullable text}, {"Labels", each List.Max([issues.fields.labels]), type nullable text}, {"User Business", each List.Max([issues.fields.customfield_34216]), type nullable number}, {"Status", each List.Max([issues.fields.status.name]), type nullable text}, {"Issue Type", each List.Max([issues.fields.issuetype.name]), type nullable text}, {"Version", each List.Max([issues.fields.fixVersions]), type nullable text}, {"Created Date", each List.Max([issues.fields.created]), type nullable datetimezone}, {"Due Date", each List.Max([issues.fields.duedate]), type nullable date}, {"Resolution Date", each List.Max([issues.fields.resolutiondate]), type nullable datetimezone}, {"Linked Issue", each List.Max([issues.fields.customfield_11900]), type nullable text}})
in
    #"Grouped Rows"

"Filled_Product_Name",
each if [Project Name] = null then
let
correspondingIssueKey = [custom field],
matchingRow = Table.SelectRows(#"Renamed Columns", each [ISSUESKEY] = correspondingIssueKey)
in
if Table.RowCount(matchingRow) > 0 then
matchingRow{0}[Project Name]
else
null
else
[Project Name] just add the custom column like the above code 

Hi Anonymous, another approach:

Result:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci3ITFbSUfJ0843UNTIyAzJ9ffwLihUCivKzUpNLkKVidaKVgkvyiyoRguZAJroSVBMtgEwfH1+gkchimOos4Tan5Rcp+AWEIkthKDc2gBirG+Tojm4uqhONDZGdCNSHRYkRii8g1oUkFmcjVBhjMQTVQSZAZnhmWqZCcGpecWZeuoJjcklmWWZJpUJQanJ+el5mSWZ+HrJyTEtMUSzBpsIMPbhjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Issue Type" = _t, issues.key = _t, #"Project Name" = _t, issues.fields.customfield_11900 = _t]),
    MergedQueries = Table.NestedJoin(Source, {"issues.fields.customfield_11900"}, Source, {"issues.key"}, "Source", JoinKind.LeftOuter),
    ExpandedSource = Table.ExpandTableColumn(MergedQueries, "Source", {"Project Name"}, {"Project Name Temp"}),
    ReplaceProjectName = Table.ReplaceValue(ExpandedSource, each [Project Name], each if List.Contains({"", null}, [Project Name]) then [Project Name Temp] else [Project Name], Replacer.ReplaceValue, {"Project Name"}),
    RemovedColumns = Table.RemoveColumns(ReplaceProjectName,{"Project Name Temp"})
in
    RemovedColumns