Forum Discussion
Convert 24 HR time
The first formula I supplied is DAX. That is the one that starts with "Column =". You use that version after you have run your query and are out of the Query Editor window and in Power BI Desktop. You go to the Data tab (middle icon on the left) and go to "Modeling" and then "New Column". That is where you would use the FIRST formula.
The SECOND formula is when you are in the Query Editor window and go to "Add Column" in the ribbon and then New Column. You seem to be using the DAX query when you should be using the M query and using the M query when you should be using the DAX query.
I received the following message: Expression error: The name 'CONCATENATE' wasn't recognized. Make sure it's spelled correctly.
M code version:
= Text.Middle(Text.From([Time]),0,Text.Length(Text.From([Time])) - 2) & ":" & Text.Middle(Text.From([Time]),Text.Length(Text.From([Time]))-2,2)
This assumes that your original data is a Number, hence the Text.From functions. If you have converted it to Text, you don't need those although the formula as presented will work with either Text or Number as the original data type for [Time] column.
I received the following error:
The syntax for '.' is incorrect. (DAX(Text.Middle(Text.From([ACTUAL_TIME]),0,Text.Length(Text.From([ACTUAL_TIME])) - 2) & ":" & Text.Middle(Text.From([ACTUAL_TIME]),Text.Length(Text.From([ACTUAL_TIME]))-2,2))).
Is it still M or DAX?
The first formula I supplied is DAX. That is the one that starts with "Column =". You use that version after you have run your query and are out of the Query Editor window and in Power BI Desktop. You go to the Data tab (middle icon on the left) and go to "Modeling" and then "New Column". That is where you would use the FIRST formula.
The SECOND formula is when you are in the Query Editor window and go to "Add Column" in the ribbon and then New Column. You seem to be using the DAX query when you should be using the M query and using the M query when you should be using the DAX query.