Forum Discussion
combine data into one row
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,
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.
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.
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,
Thank you, Simon_Hou-MSFT!
Using the Group By feature did exactly what we were trying to accomplish. Thank you for your help!
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.
