combine data into one row

[jcoe]

Hi,

I've been trying to figure this out and so far nothing I've come across has seemed to address my specific issue.

I'm trying to combine my data based on the "Task Name" column, keeping the information that is duplicated, but merging the data where a row is null, which will only be my "Project Type Description" and "Service Type Description" columns.   See my example below.

Any ideas on how I can accomplish this?

My table:

 

Desired result:

[Back2Basics]

Based on wat you have said I would assume that the project or the service fields will always only have one entry between them - eg. if project is null then service has en entry and vice versa. If that is the case I have done the following:

Add New Column

Project/Service Description = if(Mytable[Project Type Description]=BLANK(), Mytable[Service Type Description], Mytable[Project Type Description])

There might be cleaner ways of doing this but I have found this has worked for me in a similar situation.

[Anonymous]

I would head into Power Query (Edit Queries).  

Sort by a column that may have blanks... then use Fill Down.  

Sort by the next column that might have blanks... and use Fill Down.  

Repeat for each column.

Select all the rows you did the fill down trick on... and under "Remove Rows", select Remove Duplicates.

Dance.

[jcoe]

Hi Scott,

Thanks for the reply.  While your solution would indeed work in the example I gave you, our real world data can contain many different Project/Service Type Description values, so Fill Down isn't alway a workable solution.  Here are the rules I would like to apply the dataset through Power BI automatically, if possible.

1.  If Task Name rows contain duplicate values, copy Project Type Description value where not null to Project Type Description where value is null.  If both rows in the dataset contain null values, then keep it null.

(This should populate all of the null values in the Project Type Description column, unless the value is truely null)

2.  Repeat step 1 for the Service Type Description column.

(This should get the dataset in the format you proposed and give me completely duplicate rows)

3.  Remove duplicate rows 

(This should give me one row of data for each Task Name)

Hope this makes sense.

[v-sihou-msft · Accepted solution]

@jcoe

You can go to Query Editor, select both "Project Type" and "Service Type" columns, click "Group By" to have records group by all other columns and specify aggregation for these two columns. See my sample below:

Regards,

[kymramosrpo]

What a neat, easy-to-follow solution! Thank you so much!

[jcoe]

Thank you, Simon_Hou-MSFT!

Using the Group By feature did exactly what we were trying to accomplish.  Thank you for your help!

[dkay84_PowerBI]

This is the general approach I would take.  If you want to share a pbix file or paste some sample data I can probably provide more precise details.

In query editor, do a group by using ID and choose the aggregation to All Rows.

Create a function that implements the logic you describe in order to create new columns that have the values you want, then remove the original columns and remove any duplicates if necessary.

In your original table which has been grouped, invoke a function as a new column that takes in the table value (resulting from the group by).

Expand the results and remove any dupes or extra columns.

Again, if you can post some sample data, not just a screen shot, or a PBIX, I can dig in a bit deeper here.