cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
k1s1
Helper I
Helper I

Combining dates as text in a grouping step

 

Hello,

 

I'm trying to group so that I have columns containing text separated by commas, one of which will group dates producing this kind of output in each cell

 

"12 Mar 2021, 13 Apr 2021"

This is the M for the step that I have tried:

 

= Table.Group(#"Added Custom1", {"Referral Reference", "vs Target"}, {
        {"MDTs", each Text.Combine([Type],", "), type nullable text}, 
        {"MDT Dates", each Text.Combine(Date.ToText([Event Date],"dd MMM yyyy"),", "),type nullable text}
        })

 

 

This columns works fine, creating a list of text, separated by commas:

 

        {"MDTs", each Text.Combine([Type],", "), type nullable text}, 

 

 

So I tried to adapt it for the dates as below but this for this column I get and "Expression.Error: We cannot convert a value of type List to type Date."

 

{"MDT Dates", each Text.Combine(Date.ToText([Event Date],"dd MMM yyyy"),", "),type nullable text}

 

 

I was assuming I could simoply wrap the date field with Date.ToText, but that didn't seem to work.  What is the correct syntax /  approach?

 

 

1 ACCEPTED SOLUTION
Rocco_sprmnt21
Super User
Super User

  • the Text.Combine function takes a LIST as input and concatenates the various elements in a single text string.
  • the date.totext function takes a DATE TYPE value as input.

 

 

in the following expression, you are asking the Date.ToText function to work on a list represented by the column content [Event Date]

{"MDT Dates", each Text.Combine(Date.ToText([Event Date],"dd MMM yyyy"),", "),type nullable text}

 

prova (non ho testato il codice: potrebbero esserci errori di sintassi) a cambiarla nel seguente modo, per consentire alla funzione di agire individualmente su ogni singolo elemento della lista:

 

{"MDT Dates", each Text.Combine(List.Transform([Event Date], (dtt)=>Date.ToText(dtt,"dd MMM yyyy")),", "),type nullable text}

 

View solution in original post

2 REPLIES 2
k1s1
Helper I
Helper I

Thank you

Rocco_sprmnt21
Super User
Super User

  • the Text.Combine function takes a LIST as input and concatenates the various elements in a single text string.
  • the date.totext function takes a DATE TYPE value as input.

 

 

in the following expression, you are asking the Date.ToText function to work on a list represented by the column content [Event Date]

{"MDT Dates", each Text.Combine(Date.ToText([Event Date],"dd MMM yyyy"),", "),type nullable text}

 

prova (non ho testato il codice: potrebbero esserci errori di sintassi) a cambiarla nel seguente modo, per consentire alla funzione di agire individualmente su ogni singolo elemento della lista:

 

{"MDT Dates", each Text.Combine(List.Transform([Event Date], (dtt)=>Date.ToText(dtt,"dd MMM yyyy")),", "),type nullable text}

 

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors