Power Query Combine list of dates

[dicken]

Hi,
I've been trying for some time, on an off, to concatenate a list of dates, the grouped data I have is like so;

Table.Group(Source, {"Product"}, {{"Count", each List.Sum([Sales]), type number},

                      {"st", each List.StandardDeviation([Sales]), type number},

                          { "who sold what " , each Text.Combine( List.Distinct( [Sales rep]) ," , " ) }} )

but in the last arguemtn I need to combine a list of dates not text, so Text.Combine will not work, I've tried wrapping in
Date.ToText, but this just returns an error.
Any Suggestions?

 

[dicken]

Follow up to my question, concatenate dates,
Just realized, if you don't have a data type , means you can just use the drop down to extract the data and use a customer delimiter,
but if anyone knows of a date equivalent of text.combine, I'd still like to know.

Richard.

 

[JGordon11]

let
    Source = Table.TransformColumnTypes(Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc1BCsMwDATArwTRo0GWZEnOse0zTP7/jcaJhQnubWHY3dbgAwm2F6VdbDujIVXkzARHavAeaPVCR7aJ0SwqHYmRfFVm7yp4GT9mPYzyxG98Zhn4p1ncbuQ6MS6tlI6Ksq6q+m26Gu2jx/F4/AA=", BinaryEncoding.Base64), Compression.Deflate)), type table [Rep, Sales, Date]),{{"Date", type date}, {"Sales", Currency.Type}}),
    Group = Table.Group(Source, {"Rep"}, {{"Sales", each List.Sum([Sales]), Currency.Type}, {"Dates", each Text.Combine(List.Transform([Date], Date.ToText), ", "), type text}}),
    Sort = Table.Sort(Group,{{"Rep", Order.Ascending}})
in
    Sort

 

[dicken]

let
    Source = Table.TransformColumnTypes(Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc1BCsMwDATArwTRo0GWZEnOse0zTP7/jcaJhQnubWHY3dbgAwm2F6VdbDujIVXkzARHavAeaPVCR7aJ0SwqHYmRfFVm7yp4GT9mPYzyxG98Zhn4p1ncbuQ6MS6tlI6Ksq6q+m26Gu2jx/F4/AA=", BinaryEncoding.Base64), Compression.Deflate)), type table [Rep, Sales, Date]),{{"Date", type date}, {"Sales", Currency.Type}}),
    Group = Table.Group(Source, {"Rep"}, {{"Sales", each List.Sum([Sales]), Currency.Type}, {"Dates", each Text.Combine(List.Transform([Date], Date.ToText), ", "), type text}}),
    Sort = Table.Sort(Group,{{"Rep", Order.Ascending}})
in
    Sort

 

[dicken]

Since last post iv'e got as far as nesting list tranform,

So

= Table.Group(#"Inserted Start of Month", {"Start of Month"}, {{"Count", each _, type table [Date=nullable date, Product=text, Sales=number, Start of Month=date]} , { "Total Sales" , each List.Sum( [Sales] ) }
,{"Product Sold", each Text.Combine( List.Distinct( [Product] ),", ")  } ,
           {"Dates of Sales" , each 
                              Text.Combine(
                                     List.Distinct(
                                          List.Transform( [Date], Text.From ) )," , " )    }} )

But am struggling with uisng it in an AddCustom column, Just, pasting the clearly
List.Transform( [Date], Text.From )
doesn't work, any suggestions,
Richard.

 

[JGordon11]

The problem may be that you don't have a [Date] column, rather you have a [Date of Sales] column. Try using List.Transform([Date of Sales], Text.From ) instead.

 

[dicken]

The problem may be that you don't have a [Date] column, rather you have a [Date of Sales] column. Try using List.Transform([Date of Sales], Text.From ) instead.

Duhh, yes well spotted, the [Date] was the name of the list within the list not the list of lists to be looked at.
any advice re Text.From (ms docs , not a great help) so if you can recomentd anything to look at read , let me know ,

Thanks for all the help,
Richard.

 

[JGordon11]

Text.From is useful if you want to use a function that requires a parameter of type text on something that is not of type text (such as a date). Text.From converts the value to type text and then the particular function can be used on the converted value. Other value types that can be converted to type text are number, time, datetime, datetimezone, logical, duration and binary value.

In your case we wanted to use Text.Combine on a list of values of type date. Since Text.Combine expects a list of values of type text, it won't work directly. First we need to transform the list to a list of values of type text using List.Transform and Text.From (or Date.ToText) functions.

While VBA allows a little leeway with type, eg. it can handle "1" + 1 and return 2, M code is very particular about types and will return the "We cannot apply operator + to types Text and Number" error if you try that. If the function documentation says a parameter must be a certain type and it is passed a parameter not of the specified type the compiler will throw an error. The first parameter of Text.Combine is doubly finicky - 1) it must be of type list, 2) each item in the list must be of type text.

 

[dicken]

Text.From is useful if you want to use a function that requires a parameter of type text on something that is not of type text (such as a date). Text.From converts the value to type text and then the particular function can be used on the converted value. Other value types that can be converted to type text are number, time, datetime, datetimezone, logical, duration and binary value.

In your case we wanted to use Text.Combine on a list of values of type date. Since Text.Combine expects a list of values of type text, it won't work directly. First we need to transform the list to a list of values of type text using List.Transform and Text.From (or Date.ToText) functions.

While VBA allows a little leeway with type, eg. it can handle "1" + 1 and return 2, M code is very particular about types and will return the "We cannot apply operator + to types Text and Number" error if you try that. If the function documentation says a parameter must be a certain type and it is passed a parameter not of the specified type the compiler will throw an error. The first parameter of Text.Combine is doubly finicky - 1) it must be of type list, 2) each item in the list must be of type text.

Out of interest, I tried using Date.ToText, before I even knew about Text.From, but couldn't get it to work, it was only after using the
'extract' drop down and then looking at the code that I fouind Date.From.

Richard.

 

[RoryA]

This appears to be pretty much the same as this question:

Concatenate list of dates in power query [SOLVED]

Hello all, I have a query which is grouped and the last part is a list of dates which I'd like to join together, I've tried Text.Combine, but this doesn't seem to work with dates so I tride nesting Text.FromDate, no good, or I'm not using it correctly which is more than possible. So I'm...

www.excelforum.com

is it not?