Because it creates a Table in each row. We can then use all the M code Table.xxxxx functions in a Custom Column to manipulate those Tables. It's mind blowing.
Excellent. I have a question: is possible Create groups for specific dates ? Example: my field date is day by day but I need group by month (Sum values) and March I need day by day 😅 thanks
@@ExcelOffTheGrid thank you 😊 for fast response, highly appreciated but I know this feature, and as I understood from ur reply that's my need not coverd on group by tool. Am right ?
So you want to Group By and then remove duplicates inside each Group? To do that - use Group By with the operation on All Rows. Then add a custom column with the Table.Distinct function applied to each row. Finally, expand the Tables.
@@ExcelOffTheGrid actually, I tried but it's doesn't work good as when I expand the custom table all Duplicated are reflected again despite of I used table.distinct function!!
Another excellent video Mark. I was previously aware of the Text.Combine trick but was plagued by the duplicate values in the cell. You made my day today with the List.Distinct() addition. Since the need for summarizing text is quite common, do you think Microsoft will add that as an option instead of forcing us to use the Sum workaround as you present?
You're welcome Jim. It would make sense to add Text.Combine to that list (along with as many other possible options), but I think it's unlikely for quite a while. I know the Power Query team are working on some pretty big stuff at the moment, so I doubt that will revert any resources for something like this at the moment.