Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

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

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
Anonymous
Not applicable

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?

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)

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.