Forum Discussion
Merge rows by column value depending on date filter
Hi Väinämöinen,
Your direction is right, use group by can take effect. Have you click close&applied after your operation in query editor?
Regards,
Jimmy Tao
Thank you Jimmy Tao.
Yes, I "closed and applied". The problem is now this:
- in the query editor, after merging the rows I can still the original rows individually if I click on of the "Table" values of the new column "Full data" > this gave me hope that I can still use the "date" information inside this subtable as a filtering variable
- in the data view unfortunately I do not see the "Full data" column at all, and neither in the "FIELDS" pane to the right, so I cannot select for example something like "Full data"."Date" with the filter
This problem is kind of blowing up our entire project, because the filter by date (using date from the original rows by work task) and the merging of rows (i.e. merging all tasks done by the same person) are both principal requirements. This small project is a kind of pilot for our internal use of PowerBI for business reports. Stakeholders in this projects may consider opting out of PowerBI if we cannot make this work.
Some new progress at least... :)
In the query editor, I noticed that the newly created "Full data" column has a button which allows me either to expand or aggregate selected columns. The latter seems to just add counters or summations, but the expand option actually makes selected fields of the original rows (prior to grouping/merging) available in the FIELDS list of the report view -> so by choosing to expand the Date field, I now have it available for a filter!
For some reason, the name expand gave me the impression that I would lose the merging (as in fact the table is shown in data view with all the old rows once again), but instead everything is still shown merged just like I wanted to, as long as I do not include the Full data.Date field into the table visualization. If I do include it, then the table is expanded and the merging is lost, but if I don't included then the table looks OK for my purposes.
I still miss one piece of the puzzle however: expanded fields seem to be automatically converted to string and so no further calculations with them are possible. This is strange, because in the query editor view, I can still see them being recognized as date or numbers. But if I try to use an expanded field in a filter, the filters options are only list and dropdown; and if I try to use them (for example "Full data.Hours") in some calculations, I get an error that the calculation is not allowed on a string.
Any idea on how to use expanded fields with their original data type?