Forum Discussion
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 "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")
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")
3 Replies
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.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")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
