Hi Experts,
Project:
I am working on Power BI project where I am fetching data from many JIRA projects.
The only problem here is one field which is not used in a harmonized way for across all JIRA projects and i.e. Release Information.
For all JIRA projects in my report, I derived that following 6 fields are used (shown in screenshot). Most of the projects are using fix versions and versions field where few projects are using other 4 fields.
As you can see all these 6 columns are list or records.
Project:
I am working on Power BI project where I am fetching data from many JIRA projects.
The only problem here is one field which is not used in a harmonized way for across all JIRA projects and i.e. Release Information.
For all JIRA projects in my report, I derived that following 6 fields are used (shown in screenshot). Most of the projects are using fix versions and versions field where few projects are using other 4 fields.
As you can see all these 6 columns are list or records.
- Fix version –
- Versions -
- Planned Release – 1: [Records]
- Planned Release -2:
- Address in Version :
- Net Release: Value
When I tried to extract values, it gives me error so I have to expand it to new rows which create duplicates in column - 'key'. Duplicates cannot be removed as there is data in other rows which is required.
I wish to expand and extract values from all these columns separated with delimiter in same row in respective columns.
There shouldn't be any duplicates as this will not work with many to one relationship.
Here is my advance editor code:
Code:let Source = JIRA_RCAEDA_DB, #"Appended Query" = Table.Combine({Source, JIRA_Aries_DB}), #"Expanded Column1" = Table.ExpandRecordColumn(#"Appended Query", "Column1", {"key", "fields"}, {"key", "fields"}), #"Expanded fields" = Table.ExpandRecordColumn(#"Expanded Column1", "fields", {"fixVersions", "versions", "customfield_28615", "customfield_11620", "customfield_37433", "customfield_12157"}, {"fields.fixVersions", "fields.versions", "customfield_28615", "customfield_11620", "customfield_37433", "customfield_12157"}), #"Renamed Columns" = Table.RenameColumns(#"Expanded fields",{{"customfield_28615", "Address in version"}, {"customfield_11620", "Planned Release-1"}, {"customfield_37433", "Planned Release-2"}, {"customfield_12157", "Net Release"}}) in #"Renamed Columns"
Though I don't want this way but just to share content of columns, when I try after removing duplicates, it looks like this. Showing here just for last 4 columns.
This is my first Power BI project and I am at a very beginner level, so, please help me with solution and pls be kind to add details of steps.
Just FYI, I tried Group by function but I am not sure if it can be done only for two columns. Here I tried to group by one column and merge data in all other columns from duplicate rows but that didn't worked. Also, when I try Group by it take hours to execute and that may be because I have 40K+ records from JIRA.
thanks
gaurav
- Address in Version :
- Versions -