Forum Discussion
Grouping in Power Query
Dear all, Can anyone explain of how to group rows in Power Query based on whether all proper cells in column "Date" contain value or not. Example with some more information below. Thanks in ...
- Add an Index column
- Add a custom column with a formula similar to: =if [Date] <> null then [Index] else null
- If the blank dates are not "null", you may need a different comparison: eg [Date] <>""
- Fill down the custom column
- Group by {"Account no.","Entry number","Custom"}
- Then you can add a custom aggregation of a concatenated "Explanation"
So you want to remove the rows where [Entry number] starts with "TA" in this example?
I would like to achieve following:
Current situation:
Entry no Date Description TA 3 20.02.2017 Ettemaks Algsaldo Maksude
TA 3 ettemaksukonto (31.12.2016) Maksu- ja
TA 3 Tolliamet
Final situation:
Entry no Date Description TA 3 20.02.2017 Ettemaks Algsaldo Maksude ettemaksukonto (31.12.2016) Maksu- ja Tolliamet In other words to merge rows with similar Entry no in order to have the description in one cell but only in cases where the date is missing in respective cells. If there is date in all respective cells it is different description and should not be merged.
I hope it added some clarity.
Regards,
Urmas
- Add an Index column
- Add a custom column with a formula similar to: =if [Date] <> null then [Index] else null
- If the blank dates are not "null", you may need a different comparison: eg [Date] <>""
- Fill down the custom column
- Group by {"Account no.","Entry number","Custom"}
- Then you can add a custom aggregation of a concatenated "Explanation"