Consolidate/merge data in duplicate rows separated by delimiter

gmalpani

New Member
Joined
Dec 24, 2011
Messages
37
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.
Query Screenshot.JPG

  • Fix version –
    • Versions -
      • Planned Release – 1: [Records]
      • Planned Release -2:
        • Address in Version :
          • Net Release: Value
          Issue:
          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.
          Report screenshot after removing duplicates.JPG


          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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top