cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wpf_
Post Prodigy
Post Prodigy

how to convert a whole number to show 12 hour format

Looks like power query M is a complete different beast from DAX.  But I'm trying to create a new column in power query editor because i've been told it's generally better to add there then with DAX.  So I have a column of whole numbers that represent Hours of the day (0-23).  I'm trying to add a new column to convert that to 12 hour format ( 0 = 12:00AM, 13 = 1:00PM etc). Thanks.

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@wpf_ - Not sure who told you that, but it's perhaps true up until the point that it takes 10 times longer to do something than to just do it the way that you know how to do it. But, understood there are some Power Query bigots out there to be sure. But, after all, it ends up being a column in the data model regardless so it's not like it is the end of the world if you do in DAX or Power Query. Anway, you could do it like this in Power Query:

 

Time.FromText([Hour] & ":00:00")

 

Make sure your Hour column is text or you can convert it using Text.From 


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

@wpf_ - Not sure who told you that, but it's perhaps true up until the point that it takes 10 times longer to do something than to just do it the way that you know how to do it. But, understood there are some Power Query bigots out there to be sure. But, after all, it ends up being a column in the data model regardless so it's not like it is the end of the world if you do in DAX or Power Query. Anway, you could do it like this in Power Query:

 

Time.FromText([Hour] & ":00:00")

 

Make sure your Hour column is text or you can convert it using Text.From 


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

View solution in original post

@Greg_Deckler 

 

Awesome that worked!

Yea I was told by one of the guys on here that power query does a better job of compressing the data.  But it's good to hear that not everyone is on board with that.  I'm already having a hard time wrapping my head around dax, and now M.  : )

@wpf_ - Well, there is one guy in particular but I won't name names. Anyway, in that case, compression has nothing to do with Powre Query or DAX. Compression is in the Vertipaq engine. So, as you can see in the attached PBIX files, 1 was created all in Power Query and 2 was created all in DAX. 2 is actually a tiny amount smaller than 1 so whatever that guy was preaching is bogus IMHO. There are other reasons why you might want to use Power Query over DAX but compression has nothing to do with any of that.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

@Greg_Deckler ,

 

Thanks for that example.  Now I don't have to go crazy scrambling trying to learn M.  

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!