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

[Anonymous]

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 "Expression Error: We cannot apply field access to the type function ". What am I doing wrong.

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]

Hi @Anonymous 

I don't see how the accepted solution converts to IST as you asked for?

If all you wanted was a text string to represent the UTC time all you need is this

= Table.AddColumn(#"Changed Type", "Custom", each Text.Insert(Text.PadStart([#"Dept Time[UTC]"], 4, "0"),2,":"))

Phil 

[wdx223_Daniel · Accepted solution]

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]

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.