Forum Discussion

jcoe's avatar
jcoe
Frequent Visitor
9 years ago
Solved

combine data into one row

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:

 

 

Need Help
  • v-sihou-msft's avatar
    v-sihou-msft
    9 years ago

    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,

     

     

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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's avatar
      jcoe
      Frequent Visitor
      9 years ago

      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's avatar
        v-sihou-msft
        Microsoft Employee
        9 years ago

        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,

         

         

  • Back2Basics's avatar
    Back2Basics
    Resolver I
    9 years ago

    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.