Hi, I am a beginner in using Power Query.
I have a situation that I want to fill in empty Project Name column with correct Project Name value when the "issues.fields.customfields_11900" = "issues.key".
For example from the screenshot below:
1. The Project Name for second row need to be filled in with 'MLOps Project' value as IFMY-226 key matched for both column and same goes with the rest.
2. If there is no key matched, remain Project Name column as empty.
Anyone face the same situation before? Please advice.
Thanks in advance.
1 ACCEPTED SOLUTION
Hi @Beginner123, 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
"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 @ManuelBolz , your solution is working well but can I know if we possible to replace the empty field of Project Name column with Project Name Full value instead of creating a new column?
**Note = issues.fields.customfields_11900 column is renamed to Linked Issue.
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"})
in
Expand
Helpful resources
Fabric Data Days
Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!
Power BI Monthly Update - October 2025
Check out the October 2025 Power BI update to learn about new features.
FabCon Atlanta 2026
Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
