Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |