Combine Row with same ID and concatenate value of specific column

[Anonymous]

Hello Everyone,

I have the following table:

Unique ID	Column 1	Column 2	Column 3
1	A	B	C
1	A	B	D
2	X	Y	Z

I am trying to combine the rows with ID=1 as follows, keeping the similar column values (Column 1 and Column 2) and concatentating the column values that differ (Column 3). Column 3 is a String format.

Unique ID	Column 1	Column 2	Column 3
1	A	B	C,D
2	X	Y	Z

Any help would be very much appreciated

[tamerj1]

Hi @Anonymous 
You can create a table visual, drag [Unique ID], [Coulmn 1] & [Coulmn 2] into the table (don't summsrize) then place the following measure

Column 3 Measure =
CONCATENATEX ( TableName, TableName[Column 3], ", ", TableName[Column 3], ASC )

If you want to create a new calculated table

NeW Table =
SUMMARIZE (
    TableName,
    TableName[Unique ID],
    TableName[Column 1],
    TableName[Column 2],
    "Column3", CONCATENATEX ( TableName, TableName[Column 3], ", ", TableName[Column 3], ASC )
)

[Syndicate_Admin]

Hello

I am doing the same exercise, however all the rows are concatenated and not only by the id.

Any solution for this? I used the same formula:

Column 3 Measure =
CONCATENATEX ( TableName, TableName[Column 3], ", ", TableName[Column 3], ASC )

Result:

My id is further to the left, but still looking through the field: MDD2 is displayed to GENESIS GUALOTUNA with the same id but different descriptions that do concatenate well in the observations, but the last MDD2 no, the last one should only appear in Observations: By additional device.

[tamerj1]

Hello Admin @Syndicate_Admin and @Anonymous 

Please share a sample PBIX file to check what is the problem.

[Anonymous]

The data I have is being fetched from an API call, so it possible to apply transformation on the data to achieve what you suggested? By the way, I am relatively new to PowerBI.
Thanks @tamerj1 for your fast reply anyways.

[tamerj1]

@Anonymous 

There is no transformation of data whatsoever. Would you please calrify your concern. Am I missing something?

[Anonymous]

Maybe I did not put my concern in context, sorry for the confusion @tamerj1 .

Here's the situation:

I'm using new blank query to fetch data via GraphQL once the authentication is performed. Here's a sample of the query I'm using:

<Get Token + specify Post request paramters>
.....
Content=Text.ToBinary("{""query"":""query {
project {
  id
  projectCode
  procurementStage
  workPlanStatus
  projectCategory {
    name
  }
}
}""}")
]
),
#"JSON" = Json.Document(Source),
data = JSON[data],
application = data[project],
#"Converted to Table" = Table.FromList(application, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", 
{"id",
  "projectCode",
  "procurementStage",
  "workPlanStatus",
  "projectCategory"
}, 
{
  "Project ID",
  "Project Code",
  "Procurement Stage",
  "Workplan Status",
  "Project Category"
}
),

#"Expanded Column1.projectCategory" = Table.ExpandListColumn(#"Expanded Column1", "Project Category"),
#"Expanded Column1.projectCategory1" = Table.ExpandRecordColumn(#"Expanded Column1.projectCategory", "Project Category", {"name"}, {"Category"})

in
    #"Expanded Column1.projectCategory"

A sample of the "data" response is as follows:

{
  "data": {
    "project": [
      {
        "projectCode": "O-JZ-21-IO-002",
        "procurementStage": "Award",
        "projectCategory": [
          {
            "name": "Internet Services-1 (EN) "
          },
          {
            "name": "IT Infrastructure-1 (EN) "
          }
        ],
        "workPlanStatus": "WorkPlan"
      }
    ]
  }
}

Now the data presentation in the table after fetching it is in this format:

Notice how rows 4 and 5 are duplicated for the same project, the reason being this specific project belongs to two "Categories": Internet Services-1 (EN) & IT Infrastructure-1 (EN).

What I'm trying to achieve is this: combine the projects having more than 1 category into one row, with the "Category" values concatenated as follows

Project ID	Project Code	Procurement Stage	Workplan Status	Category
SPlZFbTMWbeR	O-JZ-21-IO-002	Award	WorkPlan	Internet Services-1 (EN), IT Infrastructure-1 (EN)