Date field formatting

[Anonymous]

Hello people,

I'm creating indicators based on a sharepoint list, but the list returns two types of dates, (note below)

If I change my column to date, the numeral fields give an error.

Now if I change the field to Integers and then add a new step to date, the fields that had a date before return me an error, but the numeral fields return a date.

Can anyone help me find a solution?

[Mariusz · Accepted solution]

Hi @Anonymous 

It is an odd one, no idea why this is happening but you can fix it by adding an extra "transform" step as per below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLRNzTUNzIwNFOK1YlWMjEyNwKyYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    transform = Table.TransformColumns(Source,{{"Date", each try Number.FromText( _ ) otherwise _, type date }}),
    #"Changed Type" = Table.TransformColumnTypes(transform,{{"Date", type date}})
in
    #"Changed Type"

Or optional see the attached for the ref.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[dax · Accepted solution]

Hi @Anonymous , 

It seems not to  be achieved directly, I think you could try below M code to see whether it work or not

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLRNzTUNzIwNFOK1QFyjaBcCzDX2EDf0AjEtQRzTYzMjcyQWbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.Contains([Date],"/") then 1 else 2),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Custom"}, {{"Count", each [Date], type list}}),
    #"Transposed Table" = Table.Transpose(#"Grouped Rows"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"1", type any}, {"2", type any}}),
    #"Expanded 1" = Table.ExpandListColumn(#"Changed Type", "1"),
    #"Expanded 2" = Table.ExpandListColumn(#"Expanded 1", "2"),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Expanded 2", {{"1", type date}}, "aa-DJ"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"2", Int64.Type}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Changed Type1",{{"2", type date}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type2"),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Duplicates", {}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"})
in
    #"Removed Columns"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Mariusz · Accepted solution]

Hi @Anonymous 

It is an odd one, no idea why this is happening but you can fix it by adding an extra "transform" step as per below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLRNzTUNzIwNFOK1YlWMjEyNwKyYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    transform = Table.TransformColumns(Source,{{"Date", each try Number.FromText( _ ) otherwise _, type date }}),
    #"Changed Type" = Table.TransformColumnTypes(transform,{{"Date", type date}})
in
    #"Changed Type"

Or optional see the attached for the ref.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn