Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Need Help : Expression Error: We cannot apply field access to the type function

I am trying to create a column with M- Query to convert a time field in the text to regular time in addition to converting it from UTC to IST. Using the query underneath. However, getting an error of...
Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    5 years ago
    Table.AddColumn(#"Changed Type", "Dep Time (IST)", each if Text.Length([#"Dept Time[UTC]"])=4 then Text.Start([#"Dept Time[UTC]"],2)&":"&Text.End([#"Dept Time[UTC]"],2) else if Text.Length([#"Dept Time[UTC]"])=3 then Text.Start([#"Dept Time[UTC]"],1)&":"&Text.End([#"Dept Time[UTC]"],2) else if Text.Length([#"Dept Time[UTC]"])=2 then "00"&":"&Text.End([#"Dept Time[UTC]"],2) else "00:00")
PhilipTreacy's avatar
PhilipTreacy
Icon for Super User rankSuper User
5 years ago

Hi Anonymous 

This M code will do it.

Here's a sample PBIX file containing the code.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrQwNlWK1YlWMjIwNQAzoHxjAwjX1BhCWxgC6VgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Dept Time[UTC]" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.PadStart([#"Dept Time[UTC]"], 4, "0")),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type time}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Dept Time[IST]", each [Custom] + #duration(0, 5, 30, 0))
in
    #"Added Custom1"

 

Phil 

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.