cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
robertsbd Member
Member

Lists and data types - question

Hi all,

 

Just a question toclarify my understanding of power query. Does data in a list have a type?

 

For example:

 

I have a table, one of the columns contains dates. As imported the date column is still as an undefined type.

 

I then select this column as a list and take the Max as follows:

 

List.Max(table[dates])

 

I get the correct value

 

I was just wondering how does power query know to treat these strings as dates, and is this safe, or should I be selecting the column from the table, changing its type to dates, changing this to a list and then performing a the List.Max(). 

 

However it seems that values in lists don't have a type, so even though I have converetd the dates to dates in the table before I convert it to a list, will this type information be lost when I covert it to a list before perorming List.Max()?

 

Thanks,

 

Ben

1 ACCEPTED SOLUTION

Accepted Solutions
MarcelBeug Super Contributor
Super Contributor

Re: Lists and data types - question

Yes, you can convert list data types.

In this case I would suggest to transform the values to dates using List.Transform.

 

I verified with the following code in which I get dates (in US format) from an Excel table,

changed to text (giving DateTimes as text)

then turned the date column into a list,

sorted it to check: sorted alphabetically,

then transformed into dates,

sorted it to check: sorted on date,

as a last step I checked the type: it is date.

 

The other (commented) "ListTyped" step applies Value.ReplaceType to the list, but it has the same result as the other "ListTyped" step. Value.ReplaceType won't convert the textual dates to real dates.

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Datum", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Datum", Order.Ascending}}),
    DatesList = #"Sorted Rows"[Datum],
    ListTyped = List.Transform(DatesList, each DateTime.Date(DateTime.From(_, "en-US"))),
//    ListTyped = Value.ReplaceType(List.Transform(DatesList, each DateTime.Date(DateTime.From(_, "en-US"))), type {date}),
    #"Sorted Items" = List.Sort(ListTyped,Order.Ascending),
    Check = #"Sorted Items"{0} is date
in
    Check
Specializing in Power Query Formula Language (M)

View solution in original post

2 REPLIES 2
robertsbd Member
Member

Re: Lists and data types - question

OK. I discovered with bit more playing around. If I don't covert the type before turning it to a list the sort results is actually sorting it as some kind of text string, so the Max date if I don't convert the type is 31/12/2016, but if I do convert the type it would be 01/07/2017.

 

The question is, can I convert the type in a list without changing it to a table first?

MarcelBeug Super Contributor
Super Contributor

Re: Lists and data types - question

Yes, you can convert list data types.

In this case I would suggest to transform the values to dates using List.Transform.

 

I verified with the following code in which I get dates (in US format) from an Excel table,

changed to text (giving DateTimes as text)

then turned the date column into a list,

sorted it to check: sorted alphabetically,

then transformed into dates,

sorted it to check: sorted on date,

as a last step I checked the type: it is date.

 

The other (commented) "ListTyped" step applies Value.ReplaceType to the list, but it has the same result as the other "ListTyped" step. Value.ReplaceType won't convert the textual dates to real dates.

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Datum", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Datum", Order.Ascending}}),
    DatesList = #"Sorted Rows"[Datum],
    ListTyped = List.Transform(DatesList, each DateTime.Date(DateTime.From(_, "en-US"))),
//    ListTyped = Value.ReplaceType(List.Transform(DatesList, each DateTime.Date(DateTime.From(_, "en-US"))), type {date}),
    #"Sorted Items" = List.Sort(ListTyped,Order.Ascending),
    Check = #"Sorted Items"{0} is date
in
    Check
Specializing in Power Query Formula Language (M)

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)