Fill in value if unique key match

Hello Anonymous,

i think this is your final solution:

let
    Source = Json.Document(Web.Contents("https://XXX/XXX/XXX")),
    #"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}}),
    Type = Table.TransformColumnTypes(#"Grouped Rows",{{"Issue Type", type text}, {"issues.key", type text}, {"Project Name", type text}, {"Linked Issue", type text}}),
    Columns = Table.SelectColumns(Type,{"issues.key", "Project Name"}),
    Rows = Table.SelectRows(Columns, each ([Project Name] <> "")),
    Merged = Table.NestedJoin(Type, {"Linked Issue"}, Rows, {"issues.key"}, "Move Columns", JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Merged, "Move Columns", {"Project Name"}, {"Project Name Full"}),
    ColumnProjectNameNew = Table.AddColumn(Expand, "Project Name New", each if [Project Name] = ""
        then [Project Name Full]
        else [Project Name])
in
    ColumnProjectNameNew

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